Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum formula
Greetings, all
I'm attempting to sum a column of VALUES if/when the criteria in two other columns of TEXT is met. However, the SUMPRODUCT function I'm using is not returning anything (no errors, but no sum either). =SUMPRODUCT(--($C$2:$C$4000="New York"),--($A$2:$A$4000="HOUSE"),$X$2:$X$4000) Any ideas why this particular formula isn't working, or suggestions for other means of capturing what I need? Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum formula
What do you mean by nothing? It should at least show a 0 unless you have
suppressed the display of 0s... Formula is correct... Make sure you have numbers in X2:X4000 which are formatted as numbers... You can check with this formula in Y2 =ISNUMBER(X2) and copy down "mckzach" wrote: Greetings, all I'm attempting to sum a column of VALUES if/when the criteria in two other columns of TEXT is met. However, the SUMPRODUCT function I'm using is not returning anything (no errors, but no sum either). =SUMPRODUCT(--($C$2:$C$4000="New York"),--($A$2:$A$4000="HOUSE"),$X$2:$X$4000) Any ideas why this particular formula isn't working, or suggestions for other means of capturing what I need? Thanks in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum formula
Yes, I'm getting a zero. And yes, column X contains numbers. In fact, I
have other formulas reading the same column and returning values, so that's not the issue. "Sheeloo" wrote: What do you mean by nothing? It should at least show a 0 unless you have suppressed the display of 0s... Formula is correct... Make sure you have numbers in X2:X4000 which are formatted as numbers... You can check with this formula in Y2 =ISNUMBER(X2) and copy down "mckzach" wrote: Greetings, all I'm attempting to sum a column of VALUES if/when the criteria in two other columns of TEXT is met. However, the SUMPRODUCT function I'm using is not returning anything (no errors, but no sum either). =SUMPRODUCT(--($C$2:$C$4000="New York"),--($A$2:$A$4000="HOUSE"),$X$2:$X$4000) Any ideas why this particular formula isn't working, or suggestions for other means of capturing what I need? Thanks in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum formula
Looks like you did not have New York or House where you thought you had...
"mckzach" wrote: Yes, I'm getting a zero. And yes, column X contains numbers. In fact, I have other formulas reading the same column and returning values, so that's not the issue. "Sheeloo" wrote: What do you mean by nothing? It should at least show a 0 unless you have suppressed the display of 0s... Formula is correct... Make sure you have numbers in X2:X4000 which are formatted as numbers... You can check with this formula in Y2 =ISNUMBER(X2) and copy down "mckzach" wrote: Greetings, all I'm attempting to sum a column of VALUES if/when the criteria in two other columns of TEXT is met. However, the SUMPRODUCT function I'm using is not returning anything (no errors, but no sum either). =SUMPRODUCT(--($C$2:$C$4000="New York"),--($A$2:$A$4000="HOUSE"),$X$2:$X$4000) Any ideas why this particular formula isn't working, or suggestions for other means of capturing what I need? Thanks in advance |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum formula
the SUMPRODUCT function I'm using is not
returning anything (no errors, but no sum either). Does that mean the cell is blank? If so, I'm guessing the formula is returning 0 but you have display of 0 values turned off. The usual causes for returns of 0 are unseen whitespace characters in your criteria ranges. The numbers you want to sum may be TEXT numbers. Find one row where your conditions are met. Let's assume it's row 10. Try these formulas and see if you get a result: =A10="House" =C10="New York" =ISNUMBER(X10) All 3 of those formulas should return TRUE. -- Biff Microsoft Excel MVP "mckzach" wrote in message ... Greetings, all I'm attempting to sum a column of VALUES if/when the criteria in two other columns of TEXT is met. However, the SUMPRODUCT function I'm using is not returning anything (no errors, but no sum either). =SUMPRODUCT(--($C$2:$C$4000="New York"),--($A$2:$A$4000="HOUSE"),$X$2:$X$4000) Any ideas why this particular formula isn't working, or suggestions for other means of capturing what I need? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Formula, Conditional Formula Needed | Excel Discussion (Misc queries) | |||
Conditional Formula to indicate Formula in cell | New Users to Excel | |||
Conditional Formula | Excel Discussion (Misc queries) | |||
Conditional Formula | Excel Worksheet Functions |