Count number of instances in Excel based on another cell

Question

I want to count the number of times a name appears with "Yes" in the next column.

Table sample:

Answer 1

The formula to use for that will be COUNTIFS, which lets you do a count based on multiple ranges and multiple criteria.

For your example the exact formula would be:

=COUNTIFS(B1:B1000,E1,C1:C1000,"Yes")

Where E1 is a cell with one of the names, and then E2 for the next name, and so on.

See sample here:

And here you could see the formulas:

Answer 2

You can use a PivotTable:

1. Put headers on your 2 existing columns (e.g. "Name" and "Y/N")

2. Add a 3rd Column "Count" and fill it with "1" all the way down

3. "Insert" tab -> PivotTable. It should automatically select your data. Hit OK.

4. In the "PivotTable Fields" sidebar: Drag "Name" to the Rows area, "Y/N" in Columns, and "Count" in Values