1. ## Count if (97-->)

I have three columns:

Col A: Age
Col B: Race ( B,A,E)
Col C: Gender ( M,F)

I would like to count the number of people under 25 who are Race Code B in a formula, it's driving mad!!!

2. ## Re: Count if (97-->)

For example:

=SUMPRODUCT((A1:A1000<25)*(B1:B1000="B"))

or create a pivot table based on the data.

3. ## Re: Count if (97-->)

Thanks Hans

I was playing around in the interim and got this:

{=COUNT(IF((A2:A9<15)*(C2:C9="B"),0))}

Seems to do the same thing, thanks

on another point but related, what would the syntax for, say a person between 15 and 65 and race code B?

4. ## Re: Count if (97-->)

You can use the array formula

=COUNT(IF((A2:A9>15)*(A2:A9<65)*(C2:C9="B"),0))

or the standard formula

=SUMPRODUCT((A2:A9>15)*(A2:A9<65)*(C2:C9="B"))

Persons aged 15 or 65 are not included in the result; change > to > = and < to < = (without a space in between) to include them

5. ## Re: Count if (97-->)

The advantage of the array over the sumproduct is that you can use it to calc the other statfunctions (average, min, max, stdev, etc)

Steve

6. ## Re: Count if (97-->)

Thanks Steve

I have a habit of getting these things thrown at me, so this is good to know

7. ## Re: Count if (97-->)

The secret is when you get things thrown at you:
Decide which are most important
Catch them
Run with them...

Steve

8. ## Re: Count if (97-->)

Wow, deep Steve <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> have you been sniffing the freon this morning <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

9. ## Re: Count if (97-->)

Due to "global warming concerns" we no longer use "freons" (ie Chloroflurocarbons, or CFCs) or even HCFCs (Hydrochloroflurocarbons).

We have changed to alternate blowing agents to aid in foaming and getting good insulation: Hydrocarbons (eg cyclopentane), or HFCs (Hydrofluoroarbons) like 1,1,1,3,3-pentafluoropentane (HFC-245fa) or 1,1,1,2-Tetrafluroethane (HFC-134a), in addition to the Carbon Dioxide that is produced during the reaction (from the water/isocyanate reaction).

So, No, I have not been sniffing any freon...

Steve

10. ## Re: Count if (97-->)

<img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

I knew I would get a response like that.

I'll bug out now before we get told off for holding up the serious side of the Lounge. PS expect a PM from me soon I want to ask you something about ANTS!

11. ## Re: Count if (97-->)

You could also use a DCOUNT function. You would have to specify the criteria in the spreadsheet though! This formula is VERY flexible. As you change the criteria, it updates the count!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•