=SUM(IF(F3:F80,“xxx”),IF(E3:E80,“zzz”),C3:C80) I am trying to sum c3:c80 only when its XXX in f and zzz in E What is wrong? Seconds from throwing computer out window
If you’re trying to use SUMIF function, I believe you can only use 1 criteria.
Try this =SUMPRODUCT(–(E3:E80=“zzz”),–(F3:F80=“xxx”),C3:C80) SUMIF only works with 1 criteria as VOBA said. Or use a pivot table.
use conditional sum feature or array
Mr.Good.Guy Wrote: ------------------------------------------------------- > =SUM(IF(F3:F80,“xxx”),IF(E3:E80,“zzz”),C3:C80) > > > > I am trying to sum c3:c80 only when its XXX in f > and zzz in E > > > What is wrong? Seconds from throwing computer out > window First off, you need to take out the closing parenthesis after the “xxx” and “zzz” and move these to the end of the formula. Second, you need to put equal signs after the first two arrays. Last, when you use SUM with a nested IF statement like you are, you need to enter it as an array function. To do this don’t press ENTER when you finish typing the formula, press CTRL+SHIFT+ENTER…you’ll know it worked because Excel will put brackets around your formula. When entered as an array with the equal signs and shifted closing parenthesis, your formula should look like this {=SUM(IF(F3:F80=“xxx”,IF(E3:E80=“zzz”,C3:C80)))}
Also, you can use {=SUM((($F$3:$F$80)=“xxx”)*(($E$3:$E$80)=“zzz”)*($C$3:$C$80))} Not sure if the nesting needs to be so complete, but that should work. For each cell in the first range, it will return a 1 if ‘xxx’, and 0 otherwise, for each cell in E in the same row, it will return a 1 if ‘zzz’ and 0 otherwise, and will return the value of C in the same row, multiply the three, and sum over the range.