Counting unique instances based on 2 criteria.

Hi

I have been trying to solve this formula with COUNTIFS but I am not getting the desired result. I am looking to work out how many individual students have a "Y" in column E. Everytime I try and do this it counts all the "Y"s in column E. Each student has a unique reference in Column A. Could this be a FREQUENCY formula?

I have attached a sample spreadsheet.

There are a few ways to do this.

Here's one:

=SUMPRODUCT((J2:J281="Y")/COUNTIF(A2:A281,A2:A281&""))

You could also use a pivot table (making sure to add the data to the data model) then use Distinct Count of SC Number under VALUES and FH Flag under ROWS.

Hi Thanks for getting back to me.

I have tried both solutions that you have advised but neither of them are working. With the formula it gives me a 0 result and with the pivot table it is counting all instances of "Y" with in column E. I only need the total number of students who have a "Y" in column E. Can you help further?

Attached are both solutions posted in your sample.

Humblest apologies - I had deleted a column and forgot to change the reference. Now worked a treat - many, many thanks for taking the time to help me.