COUNTIFS with ranges

edited 12/09/19 in Smartsheet Basics


   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

Y               Task 1         Worker1                 Worker2

Y               Task 2         Worker3                 Worker1

N               Task 3         Worker4

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?



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    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. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

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