How to count from a wider range by a given value in a column?
I want to count how many employees has experience of available projects, my formula is:
=COUNTIFS([Employee A]1:[Employee D]5, ="YES", [Available?]1:[Available?]5, ="YES")
I got an error: #INCORRECT ARGUMENT SET
If I change the first condition to "[Employee A]1:[Employee A]5, ="YES"", it works. Looks like COUNTIFS doesn't support a wider range crosse multiple columns.
Don't want to add up all columns because I have tens of columns in my real sheet, and "Available" column will be modified sometimes so want to make a dynamic formula to calculate.
Any thoughts and any solution??
Best Answer

Yes. If you have 5 employee columns, then you would need 5 helper columns that have the following in them:
=Available@row
Then you would reference these helper columns in your formula:
=COUNTIFS([Employee A]1:[Employee E]5, ="YES", [Helper A]1:[Helper E]5, ="YES")
You can then hide the helper columns from view to keep them from cluttering things up.
Answers

Ranges within a function must be of the same size and shape. So if you have one range that is 5 columns wide, then the rest of the ranges must be 5 columns wide. If you do not want to do individual COUNTIFS and add them together, then you would need to add some helper columns to the source sheet (which can be hidden to keep things looking clean) that replicates the data in the single column range.

Paul, thanks for the answer, I tried and it worked.
Is "Helper Column" same column as the "Available" column?

Yes. If you have 5 employee columns, then you would need 5 helper columns that have the following in them:
=Available@row
Then you would reference these helper columns in your formula:
=COUNTIFS([Employee A]1:[Employee E]5, ="YES", [Helper A]1:[Helper E]5, ="YES")
You can then hide the helper columns from view to keep them from cluttering things up.
Help Article Resources
Categories
Check out the Formula Handbook template!