Count IF Range Match to Column Header

nlewis

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?

Paul Newcome

Are you able to provide some screenshots for context?

nlewis

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

Paul Newcome

Try something along the lines of

=COUNTIFS([Asset 1]@row:[Asset 4]@row, @cell = 1)

nlewis

That works if assets were listed as rows, but in my case they have to be listed as columns. Unless I'm missing something.

Paul Newcome

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.