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

marihouse

Board Regular
Joined
Jan 14, 2004
Messages
84
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

marihouse

Board Regular
Joined
Jan 14, 2004
Messages
84
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. :oops:
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Re: I need formula help....counting with two different crite

marihouse said:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

Board Regular
Joined
Jan 14, 2004
Messages
84
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! (y)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Re: I need formula help....counting with two different crite

marihouse said:
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! (y)

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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top