# 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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,974
Messages
5,856,600
Members
431,823
Latest member
irtezazaz

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