sumif then subtract

[gewilson]

Is it possible to combine several sumif's and then subtract it from a base number? Here is my best attempt.


=sum176-(SUMIF($B10:$AF10,"PD",$B11:$AF11)+SUMIF($B10:$AF10,"FA",$B11:$AF11)+SUMIF($B10:$AF10,"PS",$B11:$AF11)+SUMIF($B10:$AF10,"PN",$B11:$AF11)+SUMIF($B10:$AF10,"F1",$B11:$AF11)+SUMIF($B10:$AF10,"P1",$B11:$AF11)+SUMIF($B10:$AF10,"F7",$B11:$AF11))

 

[fred]

what is SUM176??

 

[gewilson]

176 is the number I want the total from the sumif's to subtract from.

 

[SirJB7]

Hi, gewilson!

It's possible and your formula does it. I tried it and it works.

Doesn't it works for you or do you want a shorter/easier one, which I can't figure right now?

Something shorter but not easier is:

=176-SUMAPRODUCTO(((B$10:AF$10)=({"PD";"FA";"PS";"PN";"F1";"F7"}))*(B$11:AF$11)) -----> in english: =176-SUMPRODUCT(((B$10:AF$10)=({"PD","FA","PS","PN","F1","F7"}))*(B$11:AF$11))

Just enter the formula with Ctrl-Shift-Enter instead of Enter.

Unless you have the values PD thru F7 as a named range... then you can replace {...} for that named range.

Regards!

 

[gewilson]

Fantastic - thanks!

 

[Hui]

In the English formula above that should also be

=176-SUMPRODUCT(((B$10:AF$10)=({"PD";"FA";"PS";"PN";"F1";"F7"}))*(B$11:AF$11))