# COUNTIFS with ranges

Options
edited 12/09/19

Hi,

I have a task list with a flag column and collaborators in 5 columns, so a collaborator could be the primary responsible or up to a 5th level involved in a task. I haven't been able to use Countifs function in a formula which will retrieve from the list how many tasks a given worker is involved into, the function works well up to one column, but when I expand to 2 contiguous columns it will generate an error.

At Risk     TaskName   Assigned To 1_2    Assigned To 2_2

This returns 1:

=COUNTIFS([At Risk]:[At Risk], true, ([Assigned To 1_5]:[Assigned To 1_5]), "Worker1")

While this returns "#Incorrect argument set", I'd expect a value of 2

=COUNTIFS([At Risk]:[At Risk], true, ([Assigned To 1_5]:[Assigned To 2_5]), "Worker1")

Any thoughts?

Thanks

• ✭✭✭✭✭✭
Options

Your first error is that you need to use "Y" as your criterion for At Risk:At Risk.

=COUNTIFS([At Risk]:[At Risk], "Y", ([Assigned To 1_5]:[Assigned To 1_5]), "Worker1")

See if that produces the correct answer of 2. Then troubleshoot to see if your multiple column range will work as well. From first glance, it seems like it should.

• ✭✭✭✭✭✭
Options

Multiple columns don't work in COUNTIF(S) or SUMIF(S). You would have to count each column separately and add them together.

=COUNTIFS([At Risk]:[At Risk], "Y", ([Assigned To 1_5]:[Assigned To 1_5]), "Worker1") + COUNTIFS([At Risk]:[At Risk], "Y", ([Assigned To 2_5]:[Assigned To 2_5]), "Worker1") + COUNTIFS([At Risk]:[At Risk], "Y", ([Assigned To 3_5]:[Assigned To 3_5]), "Worker1") + etc...

• ✭✭✭✭✭✭
Options

That's what i was thinking as well, Paul. Adding those Countifs together would do the trick.