COUNTIF NESTED IN IF Function

[matthew.ng.zh]

Hi, I am trying to do a count of the qualifications of my employees based on their status:

For APO, how many of them has this particular qualification "WSQ CONDUCT CROWD AND TRAFFIC CONTROL", for instance.

I tried using this formula:

=IF('NEA 1424'!$A$2:$A$204 ="APO", COUNTIF('NEA 1424'!$AB$2:$AP$204,"WSQ CONDUCT CROWD AND TRAFFIC CONTROL"), 0)

The formula didn't work and counted everyone who has the qualification i.e. ignoring the first criteria of "APO". The returned value was 115.

Then I tested the formula on another employee type "APOE" and it returned value of 0.

Did I do something wrong?

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[matthew.ng.zh]

here you go. File attached

[Glenn Kennedy]

Use this formula in C4, copied across to E4 and down:

=SUMPRODUCT(('NEA 1424'!$D$2:$R$204='Division Qualifications Monitor'!$B4)*('NEA 1424'!$A$2:$A$204='Division Qualifications Monitor'!C$3))

Replace D3 APO(E) with APOE...

[matthew.ng.zh]

Woah, let me digest. Thanks so much