Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!!!
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count if (97-->)

    For example:

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

    or create a pivot table based on the data.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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?
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count if (97-->)

    Thanks Steve

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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Jerry

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Regards,
    Rudi

Posting Permissions

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