Forum Discussion
Counting occurrences over multiple worksheets
I am trying to create a spreadsheet for productivity numbers for staff. My workbook has a Totals Tab and a separate tab for each workday. I am struggling with trying to get one of the calculations on the totals page to work correctly.
The spreadsheet I am using for testing has November 2021 dates, so my array of tabs, excluding the Totals Page, is '11012021:11302021' I am trying to write a formula on my totals page that will count the number of times the word "Yes" will appear in cell W4 on each of my daily tabs. I have tried Count, Countif, Sumproduct and am not sure what I am doing wrong.
the formula you suggested won't work because it tries to count cells in a range that includes multiple sheets. The COUNTIF function only works with ranges on a single sheet.
To count cells across multiple sheets, you need to use a formula that includes the sheet names or references indirectly. The INDIRECT function can be used to create a reference to a sheet based on a text string. You can use this with the COUNTIF function to count cells across multiple sheets.
Here's an example formula that you can use:
=SUM(COUNTIF(INDIRECT("'"&{"11012021","11022021","11032021","11042021","11052021","11062021","11072021","11082021","11092021","11102021","11112021","11152021","11162021","11172021","11182021","11192021","11222021","11232021","11242021","11252021","11262021","11292021","11302021"}&"'!W4"),"Yes"))
This formula uses an array constant to create a list of sheet names that you want to count across, and then uses the INDIRECT function to create a reference to each sheet. The COUNTIF function then counts the number of cells that contain the word "Yes" in the specified range on each sheet, and the SUM function adds up the counts for all sheets.
Note that you can adjust the list of sheet names in the array constant to include the names of the daily tabs in your workbook. Also, make sure that you adjust the reference to the cell you want to count if it is located in a different cell than W4.
This formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&TEXT(DATES,"mmddyyyy")&"'!W4"),"Yes")+0)
can also be used to count the number of times the word "Yes" appears in cell W4 on each of your daily tabs.
The SUMPRODUCT function is used to multiply each count by 1 and then add them up. Since the COUNTIF function returns an array of counts for each daily tab, multiplying by 1 converts the array to a numerical array that can be added up by the SUMPRODUCT function.
Again, replace "DATES" with the range of dates for your daily tabs, and "W4" with the cell you want to count on each daily tab.
Note that you can adjust the reference to the cell you want to count if it is located in a different cell than W4. Also, make sure that the cell containing the word "Yes" is formatted as text on each daily tab to ensure accurate counting.
Hope one of the suggested solutions will help you!
6 Replies
Thank you! Both of your formulas work perfectly!!!
A 365 solution:
=LET( stack, TOCOL('11012021:11302021'!W4), filtered, FILTER(stack, stack = "yes"), ROWS(filtered) )the formula you suggested won't work because it tries to count cells in a range that includes multiple sheets. The COUNTIF function only works with ranges on a single sheet.
To count cells across multiple sheets, you need to use a formula that includes the sheet names or references indirectly. The INDIRECT function can be used to create a reference to a sheet based on a text string. You can use this with the COUNTIF function to count cells across multiple sheets.
Here's an example formula that you can use:
=SUM(COUNTIF(INDIRECT("'"&{"11012021","11022021","11032021","11042021","11052021","11062021","11072021","11082021","11092021","11102021","11112021","11152021","11162021","11172021","11182021","11192021","11222021","11232021","11242021","11252021","11262021","11292021","11302021"}&"'!W4"),"Yes"))
This formula uses an array constant to create a list of sheet names that you want to count across, and then uses the INDIRECT function to create a reference to each sheet. The COUNTIF function then counts the number of cells that contain the word "Yes" in the specified range on each sheet, and the SUM function adds up the counts for all sheets.
Note that you can adjust the list of sheet names in the array constant to include the names of the daily tabs in your workbook. Also, make sure that you adjust the reference to the cell you want to count if it is located in a different cell than W4.
This formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&TEXT(DATES,"mmddyyyy")&"'!W4"),"Yes")+0)
can also be used to count the number of times the word "Yes" appears in cell W4 on each of your daily tabs.
The SUMPRODUCT function is used to multiply each count by 1 and then add them up. Since the COUNTIF function returns an array of counts for each daily tab, multiplying by 1 converts the array to a numerical array that can be added up by the SUMPRODUCT function.
Again, replace "DATES" with the range of dates for your daily tabs, and "W4" with the cell you want to count on each daily tab.
Note that you can adjust the reference to the cell you want to count if it is located in a different cell than W4. Also, make sure that the cell containing the word "Yes" is formatted as text on each daily tab to ensure accurate counting.
Hope one of the suggested solutions will help you!
NikolinoDE Is there a way to do this without naming every single sheet tab, so it pulls it from every tab from first to last sheet? For example if I was adding up the contents of the same cell on each I could use =SUM('First Sheet':'Last Sheet'!F2). So how could I use the =SUM(COUNTIF(INDIRECT(""&{"Nottingham","Newcastle","Craigavon"}&"!F2"),"Y")) but using the First Sheet and Last Sheet principle. Hope that makes sense, is it doable?
I have the same question. I tried adding the 50 sheets separately but I can't do it without making mistakes.
