Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNTAIF (Excel 2003)

    hi there i am stuck on this function =COUNTAIF(Contact_Details!$Q$7:Contact_Details!$Q$ 46="WT1561")
    where am i going wrong, i just want to count how many people are on that course (WT1561) in contact details to add to reports?
    Attached Files Attached Files

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

    Re: COUNTAIF (Excel 2003)

    There is not a function called COUNTAIF. You have COUNT and COUNTA (for text), but when its conditional you only say COUNTIF.

    You need to say:
    =COUNTIF(Contact_Details!$Q$7:$Q$46,B7)
    Regards,
    Rudi

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTAIF (Excel 2003)

    Hi

    Does your report sheet always have the Course code in B7 and below? if yes, see if this help. Place this formula in the next column to get the number of occurrence

    =COUNTIF(Contact_Details!Q7:Q46,Report!B7)
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTAIF (Excel 2003)

    <P ID="edit" class=small>(Edited by rory on 21-Jan-08 13:47. Removed personal info!)</P>hi there Franciz
    using the attached spreadsheet which is slightly different are the Cleartor moor address in reports sheet i need to count in (F7:F46) how many are from cleator moor, and the same for the gender of females that are from cleator moor, do i do this the same way ?
    Attached Files Attached Files

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: COUNTAIF (Excel 2003)

    The formula for H7 would be:
    =SUMPRODUCT((Contact_Details!$F$7:$F$46="Cleator Moor")*(Contact_Details!$Q$7:$Q$46=Report!G7))
    I'm not sure what you want under the Gender column? A count of females (or males) for the course, or for those on the course who are in Cleator Moor?
    Also, please DON'T publish personal information like that - always censor the data before you post it.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    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: COUNTAIF (Excel 2003)

    The number of Females with a Cleator Moor address that have the Coursecode in G7 is:

    =SUMPRODUCT((Contact_Details!$Q$7:$Q$51=$G7)*(Cont act_Details!$F$7:$F$51="Cleator Moor")*(Contact_Details!$I$7:$I$51="F"))

    Steve

  7. #7
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTAIF (Excel 2003)

    all the data is made up it is not about a real person

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: COUNTAIF (Excel 2003)

    My apologies then - it looks very realistic. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTAIF (Excel 2003)

    Is it possible to use the CountIf function here, I have tried but it does not give the right answer?


    regards, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: COUNTAIF (Excel 2003)

    COUNTIF works for a single condition only, you cannot use it to count cells that satisfy multiple conditions.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: COUNTAIF (Excel 2003)

    You can only use COUNTIF (and SUMIF) with one criterion (Excel 2007 introduces COUNTIFS for use with multiple criteria) so no, it won't work here unfortunately.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTAIF (Excel 2003)

    Hi Both Rory and Hans,

    Got it. Thanks

    cheers, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  13. #13
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTAIF (Excel 2003)

    Hi Steve,

    I tried your formula and its return 2, but I think there are 3 "F". Not sure where does it goes wrong.

    regards, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: COUNTAIF (Excel 2003)

    Check carefully - Steve's formula returns the same results as the ones you get when applying the corresponding autofilter conditions in the Contact_Details sheet.

  15. #15
    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: COUNTAIF (Excel 2003)

    I calculate 4 females for "Cleator Moor" and Course "WT1561"
    I calculate 2 females for "Cleator Moor" and Course "WT1571"
    I calculate 4 females for "Cleator Moor" and Course "WT1581"
    I calculate 0 females for "Cleator Moor" and Course "WT1591"

    and when I filter the list this for the various courses, the numbers seem to be correct so I am confused about the discrepancy. Could you elaborate?

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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