CountIf Across Dynamic Range of Worksheets

franswa3434

Board Regular
Joined
Sep 16, 2014
Messages
73
Office Version
  1. 365
Platform
  1. Windows
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.

1730476206252.png


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.


1730476303183.png


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

1730476508776.png



1730476535782.png



1730476486471.png


Thank you all so much for your help!
 

Attachments

  • 1730476191602.png
    1730476191602.png
    4.7 KB · Views: 4
  • 1730476364477.png
    1730476364477.png
    5.5 KB · Views: 4

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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’)
 
Upvote 0
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’)
Thank you. I have updated my profile to reflect this
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,174
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top