Forum Discussion
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...
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
