CountIf Across Dynamic Range of Worksheets

[franswa3434]

Good Morning Everyone,

I'm looking for a way to do a Countif across a dynamic range of worksheets.

In the example I have screenshots to, I have 2 sites, but end users will add/subtract sites as they need.

What will not change is that there is a hidden 1st sheet ("New Site") that is used to copy worksheets as end users add sites. This copy does a sheet.count-1, so the Summation page is always at the end.

I want to be able to count the items and accessories across all worksheets (minus the summation page).

Thank you all so much for your help!

 

[NickFru]

Hello - on the item summation sheet, you can use the COUNTA with INDIRECT. List the names below in a couple of cells on this sheet (in say cell C1 & cell C2) and then use these as a reference point with the following;

Site 1
New site 2

Then, use the formula, COUNTA((INDIRECT(C1&"!$A$2:$A$100"),INDIRECT(C2&"!$B$2:$B$100")))

This will add up the both lists assuming that they are both in column A & column B as referenced above within the formula on the individual worksheets. You can keep adding further lists if necessary as long as the name on each individual worksheet is the same on the summation sheet as above in Site 1 and New site 2.

 

[Peter_SSs]

What Excel version are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)