I need formula help....counting with two different criteria

[marihouse]

The following is part of the spreadsheet I am wanting to make a formula for. I was able to make a formula showing the totals for an agegroup/sex. It is =SUMIF(f2:f60,"X",B2:B60). This returns the total for females in agegroup 37-48. The x means a person in that age group got 15 of their questionaire correct. What I want to do now is count the number of females in age group 37-48. So, I need it to look at b column and f column and if there is something besides "0" in BOTH columns, to count how many there are. I have tried everything I could think of, but can't figure out where I am going wrong. Thank you!

A B C D E F G
Male Female Age0-12 Age13-24 Age25-36 Age37-48 Age49-60
0 15 0 0 0 X 0
0 14 0 0 0 0 0
0 10 0 0 0 0 X
0 14 0 0 0 X 0
18 0 0 0 0 0 X
10 0 0 0 0 X 0
6 0 0 0 0 0 X
16 0 0 0 0 0 X
11 0 0 0 0 0 0
0 15 0 0 0 0 X
18 0 0 0 0 X 0
15 0 0 0 0 X 0
0 12 X 0 0 0 0

 

[marihouse]

Re: I need formula help....counting with two different crite

it crunched up my columns, but if somebody can help me, let me know if you need me to send it to you again spaced out correctly.

 

[Brian from Maui]

Re: I need formula help....counting with two different crite

The following is part of the spreadsheet I am wanting to make a formula for. I was able to make a formula showing the totals for an agegroup/sex. It is =SUMIF(f2:f60,"X",B2:B60). This returns the total for females in agegroup 37-48. The x means a person in that age group got 15 of their questionaire correct. What I want to do now is count the number of females in age group 37-48. So, I need it to look at b column and f column and if there is something besides "0" in BOTH columns, to count how many there are. I have tried everything I could think of, but can't figure out where I am going wrong. Thank you!

A B C D E F G
Male Female Age0-12 Age13-24 Age25-36 Age37-48 Age49-60
0 15 0 0 0 X 0
0 14 0 0 0 0 0
0 10 0 0 0 0 X
0 14 0 0 0 X 0
18 0 0 0 0 0 X
10 0 0 0 0 X 0
6 0 0 0 0 0 X
16 0 0 0 0 0 X
11 0 0 0 0 0 0
0 15 0 0 0 0 X
18 0 0 0 0 X 0
15 0 0 0 0 X 0
0 12 X 0 0 0 0

What would be your expected answer from the example provided?

 

[Aladin Akyurek]

Re: I need formula help....counting with two different crite

For the sample you posted,

=SUMPRODUCT(--(F2:F14="X"),--(B2:B14>0))

computes 2. Is this the expected result?

 

[marihouse]

Re: I need formula help....counting with two different crite

That's it!!!! Thank you so much. It works. What does the -- mean and why can't I do b:b for the whole column instead of listing the range? Not that it really matters, just wondering.

THANKS!

 

[Aladin Akyurek]

Re: I need formula help....counting with two different crite

That's it!!!! Thank you so much. It works. What does the -- mean and why can't I do b:b for the whole column instead of listing the range? Not that it really matters, just wondering.

THANKS!

You are not allowed (by design) to use whole columns in this kind of formulas.

The -- bit coerces the truth values (i.e., TRUE and FALSE) into numbers (1 and 1) the way SumProduct expects them. So:

=--TRUE ===> 1
=--FALSE ===> 0

Also:

=TRUE+0 ===> 1
=FALSE+0 ===>0