Multiple values same line COUNTIF

Question

I have a table as follows for a musical instrument casting sheet [titled Song Casting)

Here's an MWE:

+--------+------+--------+-------+------+--------+---------+
| Song   | Bass | Guitar | Drums | Keys | Vocals | Back Up |
+--------+------+--------+-------+------+--------+---------+
| Song 1 | A8   | A9     | A10   | A11  | A12    | A11     |
+--------+------+--------+-------+------+--------+---------+
| Song 2 | A9   | A10    | A11   | A12  | A10    | A11     |
+--------+------+--------+-------+------+--------+---------+
|        |      |        |       |      |        |         |
+--------+------+--------+-------+------+--------+---------+

On the full sheet, the values range from F5:P65.

On another sheet [Song Counts], I am attempting to do song counts per person.

As of right now, I am using this formula. =COUNTIF('Song Casting'!F5:P65,A16)

However, it is counting different parts on the same song as two separate songs. For example, in the MWE, the person in 'A11' would have 4 on the song count instead of 2. Is there a way to modify the formula so that it only counts one instance per song?

Answer 1

This should do the trick:

=ARRAYFORMULA(SUM(--(MMULT(--(DataRange=Criteria),TRANSPOSE(COLUMN(DataRange)))>0)))

...where:

• DataRange = your list of data ('Song Casting'!F5:P65, in your case, I believe)
• Criteria = the musician ("A16" in the COUNTIF example you provided)

You can read more on how this works here: https://www.got-it.ai/solutions/excel-chat/excel-tutorial/count/count-rows-that-contain-specific-values

Keep in mind that the website is talking about entering an array formula in Excel. The equivalent in Google Sheets is actually using the function ARRAYFORMULA.