Forum Discussion

arodriqs's avatar
arodriqs
Copper Contributor
Oct 02, 2024

COUNTIF across multiple sheets, within a specified range for each sheet

Hello hello,

 

Im creating a monthly spreadsheet to track my teams performance and one of the metrics my department tracks scales with how many shifts they worked.

 

My goal was to use COUNTIF to simply count how many times each of their names pop up on each of the daily tabs and plug that into my formula.

 

The issue is that while their names will populate within the same range A2:A18 they may not be in the same places since some work different days than others. For example

09/01                 09/02
John                   John
Jane                   Sebastian
Sebastian

How can I set up the function to not only count through multiple sheets but also not give me an error if the value isnt exact just cause of the nature of the how the names are organized?

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    arodriqs It would be better if you share a sample file showing your desired output. Then contributors can understand your problem clearly and put best answer. However, from my assumption, I have putted few of a name to 3 sheets and shown how to count them in a summery sheet. See the attached file.

     

    =LET(x,TOCOL(Sheet2:Sheet4!A2:A100,1),GROUPBY(x,x,COUNTA,,0))

     

     

     

     

Resources