Conditional Sum formula

M

mckzach

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
 
S

Sheeloo

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
 
M

mckzach

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.
 
T

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top