CODEWITHSUNDEEP
google-sheetscountif
Mr L Fenner
Mr L Fenner

Reputation: 109

Countifs across multiple sheets

I am trying to count where multiple occurrences happen across multiple sheets. Example, where different teachers in a list award different colour grades:

Mrs M - 36 Greens Mr D - 12 Greens

I think I would need to use a countifs function and am fairly certain I could use something like this =COUNTIF({'Phase 1'!$I$16:$I$118,'Phase 2'!$I$4:$I$106,'Phase 3'!$I$4:$I$106,'Phase 4'!$I$4:$I$106,'Phase 5'!$I$4:$I$106,'Phase 6'!$I$4:$I$106},C13) with a few tweaks..

The big question is though, rather than adding ranges all the time, can I make it so the new tabs are automatically included in the formula..?

Thanks

Upvotes: 0

Views: 407

Answers (1)

player0
player0

Reputation: 1

no. this is the only way how to construct array {} without scripts. but you have option to create a "generator". it would go like this:

={""; ARRAYFORMULA("=INDEX(COUNTA(IFNA(QUERY({"&JOIN("; ", "'Phase "&SEQUENCE(B1)&"'!"&B2)&"}&"""";
 ""where Col1 = '""&C13&""'""; 0))))")}

enter image description here

fx generator

Upvotes: 1

Related Questions