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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!