COUNTAIF (Excel 2003)

Topic

New Reply

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?

Reply | Quote

Replies

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)

Reply | Quote

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)

Reply | Quote

(Edited by rory on 21-Jan-08 13:47. Removed personal info!)

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 ?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

My apologies then – it looks very realistic.

Reply | Quote

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

regards, fy

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Hi Both Rory and Hans,

Got it. Thanks

cheers, fy

Reply | Quote

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

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

Steve

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hi Steve,

My apology. I may have accidentally erased one of the Course when I was trying to work on it using CountIf. I reloaded the file and your formula is correct.
My blunder

cheers, fy

Reply | Quote

Hi

I am slightly late on this, I have tried and found another option which is to use the SUM formula :

In H7, to get the count for Cleator Moor. Place this and copy down

=SUM(IF(Contact_Details!F7:F51=”Cleator Moor”,IF(Contact_Details!Q7:Q51=G7,1,0),0))

and the count on ‘F” by using this in I7 and copy down the column:

=SUM(IF(Contact_Details!F7:F51=”Cleator Moor”,IF(Contact_Details!I7:I51=”F”,IF(Contact_Details!Q7:Q51=Report!G7,1,0),0)))

Both these are array formulas meaning that you need to press Ctrl, Shift and Enter all at once

cheers, fy

Reply | Quote

If you are going to copy the formula down the column, you should have have the references to the source “locked” or else it will change and not read all the cells:

=SUM(IF(Contact_Details!$F$7:$F$51=”Cleator Moor”,IF(Contact_Details!$I$7:$I$51=”F”,IF(Contact_Details!$Q$7:$Q$51=Report!G7,1,0),0)))

Steve

Reply | Quote