SUMIFS for multiple criteria...

hi, i need to test two different conditions (for both to be TRUE) to result in a sum being displayed. I've tried this formula, but despite both conditions being TRUE, I just get 0 instead of the value in BO69.....

=SUMIFS($B$1="yes", TRUE, Table 1::C69, TRUE, Table 1::BO69)

$B$1 is a drop down list with the word "yes" as an option

Table 1::C69 is a checkbox

Table 1::BO69 is the value if both the conditions are TRUE

MacBook Pro (15-inch Early 2011), Mac OS X (10.7.3)

Posted on Nov 1, 2014 4:31 AM

21 replies

Nov 6, 2014 7:06 PM in response to ric frankland

I can say categorically that it's not a good idea to use SUMIF or SUMIFS when you are only working on a single row or block of rows constituting one situation.

For example, it's like adding two numbers with the SUM function rather than just using the addition operator. That is to say, why use =SUM(A,B) when you can write =A+B ? I know this is a gross simplification, hut I hope you get the idea.

Jerry

ric frankland Author

Level 2

380 points

Nov 7, 2014 12:38 AM in response to Jerrold Green1

i get that, thanks Jerry, but i'm still not sure if i could have used anything other than SUMIF in this case....

here is a typical formula that i'm using:

Nov 7, 2014 8:00 PM in response to ric frankland

You could just as easily written:

=IF($C$1="01-01", somecellreference, 0) + ...

Jerry

ric frankland Author

Level 2

380 points

Nov 10, 2014 6:46 AM in response to Jerrold Green1

cheers Jerry, I'll try that.

do you happen to know how to solve my last query... that i posted on the 4th?

(creating a zero result if another cell has a value >0)

t quinn

Level 5

7,063 points

Nov 10, 2014 7:52 AM in response to ric frankland

Hi ric,

Charles was addressing it way back with:

if(cell>0,0,0).

This is what he meant:

if(testcell>0,0,your long sum if series or Jerry's shorter one).

So the second part of the IF is the entire formula you have now.

quinn

ric frankland Author

Level 2

380 points

Nov 15, 2014 1:49 AM in response to t quinn

thanks Quinn!

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

SUMIFS for multiple criteria...