Forum Discussion
Site Count
I have 10,000 assets listed across 140 sites, each asset have varying frequency. I would like to count the number of sites that each asset type exists on based on there frequency and task type
In the example I've created all the information is in one TAB however in my spreadsheet the the criteria exists across multiple TABS
Thank you in advance
5 Replies
excel 2016 has other tools that you can use to solve the scenario:
https://www.microsoft.com/en-us/microsoft-365/blog/2015/09/10/integrating-power-query-technology-in-excel-2016/#:~:text=In%20Excel%202016%20we%20integrated%20the%20Power%20Query,Data%20tab%2C%20under%20the%20Get%20%26%20Transform%20section.
https://support.microsoft.com/en-us/office/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b
https://support.microsoft.com/en-us/office/consolidate-data-in-multiple-worksheets-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b
I'd recommend putting all the data into a single database if that's at all possible. Excel works very well and very easily with single databases, sifting and sorting, counting, etc. Unless there's a compelling reason to have multiple tabs, you'd be far better served with a single tab containing all the data.
In this case, too, you want a single column reflecting "Frequency", not three separate ones, one each for Daily, Weekly or Monthly. That enables a single criterion -- Frequency -- to be used in the FILTER function. (By the way, the FILTER function does require the most current version of Excel)
- Thanks mathetes
=COUNT(_xlfn._xlws.FILTER(Table1,(Table1[Site type]=G2)*(Table1[Task]=G3)*(Table1[Frequency]=G4)))
I'm sure I can get this to work in my table as the majority of the data is in the main table, however when I open the sample attached the count does not apper to be working?
Thanks
AndyThis is the formula as it left me. As noted, it does require the most current version of Excel; do you have that?
=COUNT(FILTER(Table1,(Table1[Site type]=G2)*(Table1[Task]=G3)*(Table1[Frequency]=G4)))
I'm not sure what the "_xlfn._xlws." came from in the version you show.
Here's a link to a YouTube video that explains the FILTER function. https://www.youtube.com/watch?v=9I9DtFOVPIg
