0

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

Mr L Fenner
  • 105
  • 1
  • 1
  • 7
  • Please add a sample sheet with sample data so we can replicate your goal precisely & also please add a sample actual desired result with those sample data. See (How to create a Minimal, Reproducible Example) https://stackoverflow.com/help/minimal-reproducible-example. – SputnikDrunk2 Jul 06 '21 at 16:55

1 Answers1

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • Hi how would this apply to an example, and could it be adapted to run countifs? – Mr L Fenner Jul 07 '21 at 12:01
  • @MrLFenner not sure whats your question here. idea is to make a copy of the spreadsheet from the answer, then edit cells B1 and B2 as you need and copy formula in B4 and paste it into your sheet – player0 Jul 07 '21 at 20:53