Countifs across multiple columns

I want to count how many times Name=xx, has Green/Yellow/red balls across 7 columns

=COUNTIFS({C-test Creation House applications Range 5}; [Column4]13; {C-test Creation House applications Range 6}; [Column5]12)

(the range 5 is the reviewer column, and the range 6 are the 7 columns)

It works if I only choose 1 column, however, when I choose the range of 7 columns, I get the error #INCORRECT ARGUMENT SET

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

That is correct. If you are only referencing one name column, then the other references within the function will need to be a single column.

Add a series of COUNTIFS together, you would write your COUNTIFS for the first ryg column then ADD a COUNTIFS for the second ryg column then add another for the third, so on and so forth.

=COUNTIFS({Other Sheet Name Column}, Name@row, {Other Sheet RYG Column 1}, "Green") + COUNTIFS({Other Sheet Name Column}, Name@row, {Other Sheet RYG Column 2}, "Green") + COUNTIFS({Other Sheet Name Column}, Name@row, {Other Sheet RYG Column 3}, "Green") + ............................................................

All comments

Paul Newcome

That is because your ranges have to match in size. You can either add in a "helper" column (that can later be hidden) on the source sheet for each of the three colors that counts them up per row then use a SUMIFS on the target sheet, or you can write a series of COUNTIFS that looks at each column individually and add them together.

Christine Rosenberg

hi @Paul Newcome

When you say "match in size" do then mean that range 5 and 6 have to identical number of columns?

I tried to make a series of COUNTIFS, but somehow I missed something, cause I cant get it to work.

I'll give it another go :-)

Paul Newcome

That is correct. If you are only referencing one name column, then the other references within the function will need to be a single column.

Add a series of COUNTIFS together, you would write your COUNTIFS for the first ryg column then ADD a COUNTIFS for the second ryg column then add another for the third, so on and so forth.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.