# Sumproduct mystery

#### ceej75

##### New Member
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

##### Well-known Member
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

##### Well-known Member
=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

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

#### ceej75

##### New Member
=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

##### MrExcel MVP
=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

##### Well-known Member
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

##### New Member
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

##### MrExcel MVP
So you tried this version?

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

does that give you zero?

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

##### MrExcel MVP
Does this...

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

succeed?

1,081,690
Messages
5,360,614
Members
400,592
Latest member

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...