Conditional sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using the Conditional Sum Wizard to calculate sums based on text criteria.

It usually works fine except in only a few cells does it return 0 when I
know the sum is more than 0. I've rechecked formula and it appears no
different than the formulas that are working properly.

Any ideas why I'm getting 0 when it should actually be 400?

Here's the formula that I'm using. and I am pressing control+enter+shift to
make it an array formula.

=SUM(IF(Expenditures!$C$24:$C$192="Events",IF(Expenditures!$D$24:$D$192="IA",Expenditures!$F$24:$F$192,0),0))
Thanks
 
Are you sure there are no trailing spaces in column C or D?

You could try incorporating the TRIM function into your formula:
=SUM(IF(TRIM(Expenditures!$C$24:$C$192)="Events",IF(TRIM(Expenditures!$D$24:$D$192)="IA",Expenditures!$F$24:$F$192,0),0))

Just as an FYI, you can also use SUMPRODUCT for multiple condition tests.
If you have several conditions, you can run into the 7 level nesting limit
w/the IF statements.
=SUMPRODUCT(--(TRIM(Expenditures!$C$24:$C$192)="Events"),
--(TRIM(Expenditures!$D$24:$D$192)="IA"),Expenditures!$F$24:$F$192)
 
Back
Top