Sumproduct mystery

[ceej75]

Hello all, I'm a bit stumped, hopefully someone can help me out.

On a prior project I used
=SUMPRODUCT((O253:O543="x")*(R253:X543="x")) to return the number of minority males O253543 had an "x" if the person was male and R253:x543 had an "x" in one of the column if the person was a minority. R was Black, S was Hispanic, T was asian and so on.
It worked just fine.

Today on a similar project I assigned numbers to the race so that I didn't have so many columns to deal with, so now instead of having columns r-s representing minority groups, I only have one column and the numbers 1-7, each one assigned to a different group.

=SUMPRODUCT((N3:N18="Male")*(O3:O18="1"))
Its not working... it keeps coming back with zero.
N column will always have either male female or unknown in it
O column will always have 0-7 in it. The 0 represents then unknown.

Thanks in advance for any thoughts.

Ceej.

 

[Special-K99]

Maybe
=SUMPRODUCT((N3:N18="Male")*(O3:O18=1))

if this doesnt work check for trailing spaces in column N
or if this is web related for the existence of CHAR(160) (HTML space) in column N

 

[seenfresh]

=SUMPRODUCT((N3:N18="Male")*(O3:O18="1"))

I believe it is because you are comparing the value of one as a text value vs. numeric values in your range. Try typing the number 1 into an empty cell and refer to it in your formula...

 

[ceej75]

Thanks Special K, tried both suggestions, still is coming back as "0".

 

[ceej75]

=SUMPRODUCT((N3:N18="Male")*(O3:O18="1"))

I believe it is because you are comparing the value of one as a text value vs. numeric values in your range. Try typing the number 1 into an empty cell and refer to it in your formula...

Thanks....
Can you flesh that out a bit? I'm not sure I understand your fix. I do understand what you think the problem may be though.

 

[barry houdini]

=SUMPRODUCT((N3:N18="Male")*(O3:O18=1))

Dd you try removing the quotes [around 1]? Numbers don't typically require quotes, text does.

 

[Special-K99]

Make sure you have data in there that looks like "Male" and 1

Take out one of the SUMPRODUCT formula parts so you can isolate which bit is failing, ie

does
=SUMPRODUCT((N3:N18="Male")) produce a non zero value?
does
=SUMPRODUCT((O3:O18=1)) produce a non zero value?

 

[ceej75]

ok.. i've tried all suggestions and combinations of.
I feel like its just some random little glitch that I'm missing. I have the male/female/unknown formated as text and the numbers formated as numbers. They were set up as just general on both accounts.

Any more ideas?

Thanks all.

 

[barry houdini]

So you tried this version?

=SUMPRODUCT((N3:N18="Male")*(O3:O18=1))

does that give you zero?

What about this?

=SUMPRODUCT((O3:O18=1)*1)

 

[Aladin Akyurek]

Does this...

=SUMPRODUCT(--(TRIM(N3:N18)="Male"),--(TRIM(O3:O18)+0=1))

succeed?