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

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

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

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

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