Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum
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(Expe nditures!$D$24:$D$192="IA",Expenditures!$F$24:$F$1 92,0),0)) Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum
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",I F(TRIM(Expenditures!$D$24:$D$192)="IA",Expenditure s!$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"),Expenditure s!$F$24:$F$192) "Monkandy" wrote: 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(Expe nditures!$D$24:$D$192="IA",Expenditures!$F$24:$F$1 92,0),0)) Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
Conditional Format Not Working | Excel Discussion (Misc queries) | |||
Conditional formula - language needed | Excel Worksheet Functions | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |