SUMIFS for multiple criteria...

btw, i need a number of these formulas on the same line, so it would look something like this:

=SUMIFS($B$1="yes", TRUE, Table 1::C69, TRUE, Table 1::BO69) + SUMIFS($B$1="no", TRUE, Table 1::C69, TRUE, Table 1::BO70) + SUMIFS($B$1="maybe", TRUE, Table 1::C69, TRUE, Table 1::BO71)

i tried this.....

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

it works, but not when there is more than one formula :-/

thanks everyone...!!

i still couldn't get the SUMIFS to work, so using Wayne's example, this is what i found did the job:

IF(AND($B$1="yes", Table 1::C69,TRUE), Table 1::BO69)+IF(AND($B$1="no", Table 1::C69,TRUE), Table 1::BO69)

hi, another quick question....

still using the same formula above (testing multiple criteria), how can i make the result 0 (zero) if a completely different cell in my table has a value (i.e.. >0)?

so, if this different cell has any value in it more than zero, the cell will show as being zero.

cheers

ric

thanks Charles, i've just tried that but it comes back with an error :-(

if i was to nest an IF function, would this need to be nested against each of the other IF functions in the long formula i have ...?

IF(AND($B$1="yes", Table 1::C69,TRUE), Table 1::BO69)+IF(AND($B$1="no", Table 1::C69,TRUE), Table 1::BO69)

I've tried it a few different ways but it either does nothing (still shows a figure and not zero) or comes back with a syntax error.

as you can probably tell, i only have a very basic knowledge of formulas, but it almost needs another command at the end of the formula to say something like... OR IF(cell>0,0).... ?!

hi Yellowbox, no problem, I've used SUMIF elsewhere and it works well, but obviously this requirement is a little different and I could only get IF to work. Now that it does, I realised that I needed a similar formula to show 0 (zero) if the original cell had a value in it. It's all to do with options!

plus it's all getting very complicated!

here are some screen grabs that will hopefully make sense (cell and formula)....

this is my standard formula for all items on my sheet.

the cost will vary depending on which building is selected in C1.

(missing row names are 1 for the building type, 54 for electric heating and 55 for water heating)

if a checkbox is selected on a different sheet, then the option will be water heating, otherwise it's electric.

however, the cost for the electric still shows in the table, and i want this to change to 0 (zero). I thought the easiest way was to test the cell directly below, so that if it showed any figure more than 0, then the value for electric heating would be 0.

hope this helps!

apologies - a few extra images of the formula have appeared somehow and I can't edit to remove them!

thanks Jerry! i'm learning...!

so with my latest query, on row 54 for electric heating, should i avoid the SUMIF function?

please ignore the first and last formula images - they shouldn't be there and stupidly i can't edit the post...