Range with multiple columns with COUNTIFS

Al ISS

One range in my COUNTIFS formula has several columns included. If I use this range on COUNTIF (no S on it), it works perfectly but, when I include in COUNTIFS, it does not work. (#Incorrect Argument Set). I then split all these columns in one range, criterion and now does not count anything. Do you have any information on how I can make COUNTIFS read this group of columns as COUNTIF does?

Range 2 include 9 columns side by side.

Range 1 is just one column

=COUNTIFS (Range 2, "1", Range 1, "4") It does not work - #Incorrect Argument Set

=COUNTIF (Range 2, "1") It works perfectly.

=COUNTIFS (Col1:Col1, "1", Col2:Col2,"1",......Col9:Col9,"1", Range 1, "4") =0, does not count anything.

Thank you.

Al

Paul Newcome

That is because each range must be the same size. Since COUNTIF uses only 1 range, it will work, but when you move to COUNTIFS and include another range that is only a single column, it is going to throw the error because the ranges do not match.

To count multiple columns that could contain "1" alongside the single column containing "4", you would need to do one of two things...

Either you can use multiple COUNTIFS and add them together:

=COUNTIFS({First Column}, "1", {Range 1}, "4") + COUNTIFS({Second Column}, "1", {Range 1}, "4") + COUNTIFS({Third Column}, "1", {Range 1}, "4") + COUNTIFS({Fourth Column}, "1", {Range 1}, "4") + COUNTIFS({Fifth Column}, "1", {Range 1}, "4")

OR

You could add in some hidden helper columns on the source sheet that replicate the column that holds the "4". One for each column that you want to count so that both ranges are the same size.

Paul Newcome

That is because each range must be the same size. Since COUNTIF uses only 1 range, it will work, but when you move to COUNTIFS and include another range that is only a single column, it is going to throw the error because the ranges do not match.

To count multiple columns that could contain "1" alongside the single column containing "4", you would need to do one of two things...

Either you can use multiple COUNTIFS and add them together:

=COUNTIFS({First Column}, "1", {Range 1}, "4") + COUNTIFS({Second Column}, "1", {Range 1}, "4") + COUNTIFS({Third Column}, "1", {Range 1}, "4") + COUNTIFS({Fourth Column}, "1", {Range 1}, "4") + COUNTIFS({Fifth Column}, "1", {Range 1}, "4")

OR

You could add in some hidden helper columns on the source sheet that replicate the column that holds the "4". One for each column that you want to count so that both ranges are the same size.

Al ISS

Paul, I was able to do it following the first option. It is became a big formula, but it works. Thank you.😀

Paul Newcome

Happy yo help! 👍️

Please don't forget to mark my answer as "helpful". This lets others searching for a similar solution know that one may be found here.