Sumproduct mystery

ceej75

New Member
Joined
Jun 14, 2010
Messages
8
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 O253:eek:543 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
Joined
Nov 7, 2006
Messages
8,327
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
Joined
Jul 12, 2006
Messages
1,141
=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
Joined
Jun 14, 2010
Messages
8
Thanks Special K, tried both suggestions, still is coming back as "0".
 

ceej75

New Member
Joined
Jun 14, 2010
Messages
8
=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.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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
Joined
Jun 14, 2010
Messages
8
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
Joined
Mar 23, 2005
Messages
20,825
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)
 

Forum statistics

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

Some videos you may like

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