# Count occurrences across multiple sheets

Copper Contributor

# 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

5 Replies

# Re: Count occurrences across multiple sheets

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.

works perfectly

# Re: Count occurrences across multiple sheets

@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.

# Re: Count occurrences across multiple sheets

@AlyssaS
Welcome, use this formula to be able to change the reference when you drag or copied it across the columns.