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.

 

[yytsunamiyy]

Try:

=SUMPRODUCT(--(TRIM(N3:N18)="MALE"),--(O3:O18=1))

 

[ceej75]

=SUMPRODUCT((N3:N18="Male")*((O3:O18=1)*1)
gives me "#value!"

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

Neither are the correct responses

SO.. I think I'm going to go back to doing it the other way, but will keep trying things as they are presented. Now it's more of a personal mission to figure this out.

ceej.

 

[Aladin Akyurek]

=SUMPRODUCT((N3:N18="Male")*((O3:O18=1)*1)
gives me "#value!"

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

Neither are the correct responses

SO.. I think I'm going to go back to doing it the other way, but will keep trying things as they are presented. Now it's more of a personal mission to figure this out.

ceej.

1) How many records do you think you have where the range in N is equal to Male and the range in O is equal to 1 at the same time?

2) What is the result of:

=COUNTIF(N3:N18,"Male")?

3) What is the result of:

=COUNTIF(O3:O18,1)

4) What is the result of:

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