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

#### marihouse

##### Board Regular
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
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

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

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

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

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

Replies
3
Views
97
Replies
8
Views
127
Replies
3
Views
192
Replies
2
Views
97
Replies
6
Views
115

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.

### Which adblocker are you using?

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

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