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??

Find more posts tagged with

Accepted answers

Paul Newcome

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.

All comments

Paul Newcome

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.

Danmu Wu

Paul, thanks for the answer, I tried and it worked.

Is "Helper Column" same column as the "Available" column?

https://community.smartsheet.com/discussion/comment/293588#Comment_293588

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.