Formula Assist

Hello having some issues with formulas, can you help spot the error?


=COUNTIF([(OBC) Assigned OBC Coordinator]:[(OBC) Assigned OBC Coordinator], "Corrisa Evans", [(OBC) If yes, choose primary error type]:[(OBC) If yes, choose primary error type])

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Gigi_Ris

    You cannot use SUMIFS as you have it written. SUMIFS only works when the range you are summing is numeric. Since you are counting checkmarks where the value of each is one, the count and the sum will be identical. The syntax you used above is that of COUNTIFS, not SUMIFS where an additional range is expected.

    COUNTIFS([(OBC) Assigned OBC Coordinator]:[(OBC) Assigned OBC Coordinator], "Gladys Risling", [(OBC) Errors or escalation - check if yes]:[(OBC) Errors or escalation - check if yes], =1)

    If you have many names to count and you are using this formula in a column, you can add [(OBC) Assigned OBC Coordinator]@row in place of "Gladys Risling" and dynamically count the people in your column. A simple report will also count the people and could also be used in a dashboard.

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Gigi_Ris

    There is an extra range in the formula - A countif (or countifs) only needs the range-criteria pair

    =COUNTIF([(OBC) Assigned OBC Coordinator]:[(OBC) Assigned OBC Coordinator], "Corrisa Evans")

    If you intended to include another criteria with the other range, to bring your total of criteria to more than just the one, you will need to swap to COUNTIFS to do so

    Kelly

  • Gigi_Ris
    Gigi_Ris ✭✭✭

    @Kelly Moore - yes i have two things to count, tried this little number below. I am counting a check mark and want it to only count the check marks for a specific person.

    =SUMIFS([(OBC) Assigned OBC Coordinator]:[(OBC) Assigned OBC Coordinator], "Gladys Risling", [(OBC) Errors or escalation - check if yes]:[(OBC) Errors or escalation - check if yes], =1)

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Gigi_Ris

    You cannot use SUMIFS as you have it written. SUMIFS only works when the range you are summing is numeric. Since you are counting checkmarks where the value of each is one, the count and the sum will be identical. The syntax you used above is that of COUNTIFS, not SUMIFS where an additional range is expected.

    COUNTIFS([(OBC) Assigned OBC Coordinator]:[(OBC) Assigned OBC Coordinator], "Gladys Risling", [(OBC) Errors or escalation - check if yes]:[(OBC) Errors or escalation - check if yes], =1)

    If you have many names to count and you are using this formula in a column, you can add [(OBC) Assigned OBC Coordinator]@row in place of "Gladys Risling" and dynamically count the people in your column. A simple report will also count the people and could also be used in a dashboard.

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!