# How do I count instances of a value in a column but only certain rows?

I have a two dimensional array something like this:

We'll call that sheet "Requirements".

I have second sheet where the names are validated by the list on "Requirements". We'll call this second sheet "Attendance".

On the "Attendance" Sheet, I want to be able to select names and show the total number of vegetarians, for example.

If I selected "jeff", "ceri", "angus" and "lisa" on the "Attendance" sheet, the result should be 2.

How do I associate the names on the "Attendance" sheet with counted and totaled values from the table on the "Requirements" sheet?

• Are you using the Attendance sheet as a way to select the names to show? Meaning do you want to see a count of all the names shown on the Attendance who are also Vegetarians? Jan 25 '17 at 13:15
• @Karl_S Yes, that's exactly it. I can count all the names on the Requirements sheet who are vegetarian easily but I want to only count those who are listed on the Attendance sheet based on the settings from the Requirements sheet Jan 25 '17 at 14:02

## 1 Answer

In the Attendance sheet you have a couple options, all based on the formula below:

``````=COUNTA( QUERY(Requirements!A2:F, "select A where A matches '" & JOIN("|", A2:A) & "' and B = 'Yes'", 0))
``````

The first is to use that formula for each dietary restriction and make a table which shows the matches for each. The second is to have a cell, perhaps C1 have a list of the restrictions. This allows you to select what count is being returnedd:

``````=COUNTA( QUERY(Requirements!A2:D, "select A where A matches '" & JOIN("|", A2:A) & "' and " & SUBSTITUTE(ADDRESS(1,MATCH(C1,Requirements!A1:D1,0),4),1,"") & " = 'Yes'", 0))
``````

OR use the formula above and change the C1 reference to the column the formula is in for the scenario where you have a chart of the restrictions.