Countifs across multiple columns

Christine Rosenberg

Hi

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

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") + ............................................................

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.

=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") + ............................................................