• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help - something is not working with my formula

K

karynd

New Member
=SUM(SUMIFS(DATA!$B$2:$B$543,DATA!$BQ$2:$BQ$543,{"9","10"},DATA!$BT$2:$BT$543,"moderately well",DATA!$CA$2:$CA$543,{"NSW","ACT"}))

this formula when I just asked for a "9" it gave me the perfect answer but when I wanted to combined the response for both a "9" and a "10" it is only giving me the answer as if I had just had the 9
is there a way to fix this formula.
what it is doing is adding up the numbers in column b
if bq has a 9 or 10
if column bt has the words moderately well
if column ca is nsw and act - it is working and giving me this correct data
can you only do this formula with only 1 options with more than 1 choice so either do it for NSW and ACT or choose to do it for the 9 & 10?

I also need to do another formula instead of a 9 and a 10 but I will also need to if it has a 0 , 1 , 2 , 3, 4 , 5 , 6

cheers
 
Hi ,

I am not clear on your requirement.

Can you specify exactly , and comprehensively what the formula should do ?

What conditions should it look for ?

Narayan
 
Did you array enter the formula?
That is when you enter the formula press Ctrl+Shift+Enter, not just Enter
 
NARAYANK991 said:
Hi ,

I am not clear on your requirement.

Can you specify exactly , and comprehensively what the formula should do ?

What conditions should it look for ?

Narayan
Click to expand...

Hey Narayan

what I am trying to do is one column has a count so then another column has the condition of words like extremely well or moderately well and another column has the condition of different states and for the state of NSW I need to include ACT and NSW and another column has number 0 to 9 and I need to include anything that has 9 or a 10

so I am wanting to count how many have (this is in column B)
extremely well (this is in column BT)
with the state NSW and ACT (this is in column CA)
with the 9 or 10 this is in column ( BQ)
so it will count what is in column B as long as the 3 conditions are met
 
Hi ,

The following formula will work :

=SUM(SUMIFS(DATA!$B$2:$B$543,DATA!$BQ$2:$BQ$543,{9,10},DATA!$BT$2:$BT$543,"extremely well",DATA!$CA$2:$CA$543,{"NSW";"ACT"}))

The thing to remember is that when one of the criteria is a row criterion , the other has to be a column criterion.

Thus , when you use {9,10} , this is a row criterion ; hence the {"NSW";"ACT"} has to be a column criterion , using the semicolon as a separator.

If , on the other hand , you use {9;10} , this is a column criterion , and hence the {"NSW","ACT"} has to be a row criterion , using the comma as a separator.

My terminology of referring to {9,10} as a row criterion may be wrong , but the essential point is that having both the separators the same will not give the correct result.

Narayan
 
Excel normally uses {1,2} to define columns 1 and 2
and
normally uses {3;4} to define Rows 3 and 4

But in some languages it is different, damn French
 
NARAYANK991 said:
Hi ,

The following formula will work :

=SUM(SUMIFS(DATA!$B$2:$B$543,DATA!$BQ$2:$BQ$543,{9,10},DATA!$BT$2:$BT$543,"extremely well",DATA!$CA$2:$CA$543,{"NSW";"ACT"}))

The thing to remember is that when one of the criteria is a row criterion , the other has to be a column criterion.

Thus , when you use {9,10} , this is a row criterion ; hence the {"NSW";"ACT"} has to be a column criterion , using the semicolon as a separator.

If , on the other hand , you use {9;10} , this is a column criterion , and hence the {"NSW","ACT"} has to be a row criterion , using the comma as a separator.

My terminology of referring to {9,10} as a row criterion may be wrong , but the essential point is that having both the separators the same will not give the correct result.

Narayan
Click to expand...


you are a legend - thanks you so much that worked perfectly
just for my info - so does that mean that in a sumifs equation you can only have 2 conditions that can choose between more than one options ? so another words if I wanted to add a condition on another column to add up for 2 choices in that column I cannot use the comma, or the ; again or is there a different separator you would use?
 
Hi ,

I have not tested this , but my impression is that when ever there are multiple criteria , there has to be at least one criterion which uses a separator different from the others.

For example if you have 3 criteria , 2 of them can use the comma as a separator , while the third will have to use a semicolon ; conversely , if 2 out of the 3 use the semicolon as a separator , the third will have to use a comma.

This same principle will extend to 4 , 5 , 6 ,... criteria.

Narayan
 
You must log in or register to reply here.
Back
Top