How to count unique values in column A, based on multiple other colums

Question

I need help with an attendance database. I am trying to count how many different unique Student IDs are in column A if column B = "English Corner" and Column C=" September" and Column D = "2019".

For instance we have the following table:

Column A    //    Column B       //  Column C   // Column D //
Student ID  //     Event type    //  Month      //   Year   //
12345       //   English Corner  //   September //  2019    //
14432       //   Coffee Chat     //   August    //  2018    //
12345       //   English Corner  //   September //  2019    //
14432       //   Coffee Chat     //   August    //  2018    //
12345       //   English Corner  //   September //  2019    //
14432       //   Coffee Chat     //   August    //  2018    //

In this case I need a formula to count if column B = English Corner and Column C = September and column D = 2019 then how many unique student IDs are there in Column A?

Answer 1

try this:

1. Create a Pivot table from your data range
2. Use Student ID as rows, Event type as values, and Year, Month and Event type as Filters
3. Set Year = "2019", Month = "September", Event type = "English Corner"
4. Create a new Name with Name = PTRows and formula =OFFSET(Sheet2!$B$4,0,0,COUNTA(Sheet2!$B:$B)) - the data range of the Pivot table starts on the 4th row ($B$4) and the name of Sheet with the Pivot table is Sheet2
5. Put a formula =ROWS(PTRows)-2 into any cell out of the Pivot table.
6. You should see this:

Best regards, Lukas