Forum Discussion

CharlieIV's avatar
CharlieIV
Copper Contributor
Jan 03, 2024

Count occurrences across multiple sheets

I am trying to track patient visits monthly. Previously I would list each new patient then manually update their appointment number as they came in.

 

I found a way to export the daily schedule and I am simply creating lists of all the patients that visited each day within the month.

 

I was able to use the COUNTIF function to count the occurrences of each patient within the month, however each month is a different sheet and it doesn't seem like the count if function works between different sheets. 

 

Each sheet is labeled "Jan '24," "Feb '24," "Mar '24" and so on and there are obviously 12 sheets. 

 

The follow up question I would have for this is how can I set this up for multiple years without having to doom scroll to find the month I need. 

 

Thanks in advance for the help

Excel
  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor
    Jan 04, 2024

    Hello CharlieIV 
    To count occurrences across multiple sheets, you can use a combination of the COUNTIF and SUMPRODUCT functions. Here’s how you can do it:

    1. Use of Excel Formula to Countif Across Multiple Sheets:

    • In the cell where you want the count, write down the following formula:

     

    =COUNTIF('Jan ''24'!A1:A100, B1)+COUNTIF('Feb ''24'!A1:A100, B1)+COUNTIF('Mar ''24'!A1:A100, B1)

     

    • Replace A1:A100 with the range where you want to look, and B1 with the cell that contains the value you want to count.

    2. Making a List of Name of Sheets to Utilize COUNTIF Function Across Multiple Sheet:

    • Use the INDIRECT function in combination with COUNTIF and SUMPRODUCT to count occurrences across multiple sheets. Here’s an example formula:

     

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A12&"'!A1:A100"), B1))

     

    • You need to make/provide a list of your sheet names and then replace A1:A12 with the range that contains your list of sheet names, A1:A100 with the range where you want to look, and B1 with the cell that contains the value you want to count.

     

    For your follow-up question, you can create an index sheet that lists all the sheet names. You can then use the HYPERLINK function to create clickable links to each sheet. This way, you can easily navigate to the month you need without scrolling through all the sheets.

     

    • AlyssaS's avatar
      AlyssaS
      Copper Contributor
      Aug 16, 2024

      Rodrigo_ Thank you, this is super helpful! Is there a way to make the range (A1:A100 in your example) dynamic? I'd like to copy the formula across multiple columns, and return the counts for columns B1:B100, then C1:C100, etc. across the same range of sheets.

      • Rodrigo_'s avatar
        Rodrigo_
        Steel Contributor
        Aug 17, 2024

        AlyssaS 
        Welcome, use this formula to be able to change the reference when you drag or copied it across the columns.
        =SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A12&"'!"&ADDRESS(1,COLUMN(B1))&":"&ADDRESS(100,COLUMN(B1))), B1))

        here's the breakdown:
        COLUMN(B1) - It will dynamically gets the column number, when you drag,copy the whole formula across columns, this will update/adjust accordingly B1 becomes C1.
        ADDRESS(1,COLUMN(B1)) - it serves as the starting cell for COUNTIF

        ADDRESS(100,COLUMN(B1)) - end cell for COUNTIF
        INDIRECT("'"&A1:A12&"'!"&ADDRESS(1,COLUMN(B1))&":"&ADDRESS(100,COLUMN(B1))) - This part constructs the dynamic range reference based on the ADDRESS function
        COUNTIF(..., B1) - counts occurrences of the value of B1 within the given dynamic range on INDIRECT function.

Resources