1. ## 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?

2. ## 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)

3. ## 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)

4. ## 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 ?

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

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

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

8. ## Re: COUNTAIF (Excel 2003)

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

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

10. ## Re: COUNTAIF (Excel 2003)

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

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

12. ## Re: COUNTAIF (Excel 2003)

Hi Both Rory and Hans,

Got it. Thanks

cheers, fy

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

14. ## 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. ## 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 Last

#### Posting Permissions

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