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?
    – Karl_S
    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

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.