Help with SUMIF's

[ddub25]

=SUMIFS($E$27:$E$38,$A$27:$A$38,$A$86,$B$27:$B$38,"Investor")

I would like to add a calculation for when the condition is not met. If the condition is not met then I want ($H$86*G91) to be calculated. Whatever I try the syntax is always wrong.

Dan

 

[moonfish]

=if(SUMIFS($E$27:$E$38,$A$27:$A$38,$A$86,$B$27:$B$38,"Investor")=0,$H$86*G91,SUMIFS($E$27:$E$38,$A$27:$A$38,$A$86,$B$27:$B$38,"Investor")

 

[T. Valko]

=SUMIFS($E$27:$E$38,$A$27:$A$38,$A$86,$B$27:$B$38,"Investor")

I would like to add a calculation for when the condition is not met. If the condition is not met then I want ($H$86*G91) to be calculated. Whatever I try the syntax is always wrong.

Dan

Which condition? There are 2 conditions in the formula:

• A27:A38 = A86
• B27:B38 = Investor

Also, these conditions are testing ranges of cells but ($H$86*G91) is just a single cell condition.

Or, do you mean if the entire formula returns 0 then execute ($H$86*G91) ?

 

[Greg Truby]

As in if the sumifs() returns a zero, you want this? Or the sum of those values that meet the conditions plus for each row where one of the conditions is not met, that row's value in column E would be replaced with $H$86 * G91? And does the G91 then correspond to row 27, i.e. G91:G102 really?

A couple of snapshots of sample data and desired outcome would go a long way to helping members help you.

 

[ddub25]

Thanks Moonfish, Valko and Greg. Sorry for the lack of explanation. I meant that if neither conditions were met then calculate ($H$86*G91). I've tried Moonfish's formula and that seems to do what I want. Thanks Moonfish.

Dan