Count IF Range Match to Column Header
Hi,
I have a master sheet with 150 columns with unique names. In these columns are check boxes. Depending on the row this box can be checked or empty. I'd like to write a CountIFS function that allows the range to be selected that matches the specified column name. I don't want to manually select that column for the formula. I have over 150 columns and this could get tedious. I know how to write the majority of the Count IF, but how do I get the "range" portion to select based on the column header name match?
Answers
-
Are you able to provide some screenshots for context?
-
Yes,
Here is my reference sheet. I have a set number of assets that are used across projects. In my metric sheet I want to count how many assets total are used for each project. For example, if project 1 uses asset 1 and asset 2 then the total number of assets is 2.
The calculation table
-
Try something along the lines of
=COUNTIFS([Asset 1]@row:[Asset 4]@row, @cell = 1)
-
That works if assets were listed as rows, but in my case they have to be listed as columns. Unless I'm missing something.
-
The above formula counts across the row. Applying it as a column formula will give you the number of boxes checked on each row / for each project.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!