Countifs with multiple criteria

Hello, Hoping someone can review!

I am trying to write a countifs formula with multiple criteria.

I need two columns with a straight forward criteria count (Specific Sprint - Sprint 19 SD and Components - Compliance)

Then I also need to add the column titled Type with both "Story" and "Task" criteria count BUT only if the Task criteria has Points, which is in another column called "Points"

I have not even added the "only if the Task has points" part as the current formula has given me a zero count after adding the "Task" part.

=COUNTIFS({Specific Sprint}, "Sprint 19 SD", {Components}, "Compliance", {Type}, "Story", {Type}, "Task")

Answers

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭
    edited 04/02/24

    Hi @JMcGill , If the Type column is a multiselect, you will need to use HAS() instead of the way you are doing it. Right now it is trying to find Type = "Story" and Type = "Task" (alone). If Type has both Story and Task, neither criteria is true. So you could write it as:

    =COUNTIFS({Specific Sprint}, "Sprint 19 SD", {Components}, "Compliance", {Type}, AND(HAS(@cell,"Story"),HAS(@Cell, "Task")))

    You can also write it as you did, but you need to use HAS().

    Good luck and be well!

  • JMcGill
    JMcGill ✭✭

    Thank you @Scott Orsey. I typed in the below and received a #unparseable error.

    =COUNTIFS({Specific Sprint}, "Sprint 19 SD", {Components}, "Compliance", {Type}, AND(HAS(@cell,"Story"), HAS(@Cell, "Task")))

    The Type column has both Story and Tasks that I need to count. Hmmmm

    But once I get this to work, I need to add in that I only want Tasks to count IF they have a nonblank in another column named Points.

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭

    Hi @JMcGill , I've stared at your formula and don't see a problem with it. Are you certain that the three cross sheet references have been defined properly ({Specific Sprint}, {Components} and {Type})? Seems like "yes" because you had it working before, but thought I'd check.

    Also, I misunderstood your original question. If you want to count if it has Story OR Task, you'll need to swap the AND() for OR(). You could also write it as:

    =COUNTIFS({Specific Sprint}, "Sprint 19 SD", {Components}, "Compliance", {Type}, HAS(@cell,"Story")) + COUNTIFS({Specific Sprint}, "Sprint 19 SD", {Components}, "Compliance", {Type}, HAS(@cell, "Task"))

    If it's not the cross-sheet references, I'm not sure what else to check, so maybe just try my second formula.

  • JMcGill
    JMcGill ✭✭

    Hi @Scott Orsey,

    Thank you! The second formula yielded a number and not an error! Yay.

    Ok, thank you. So I need to count both "Story" AND "Task" but only if the "Task" has a number in the next column. so I would only want to count 1 task here because it's the only with with Points in that column.



  • Scott Orsey
    Scott Orsey ✭✭✭✭✭

    @JMcGill , so you'll want to modify the COUNTIFS in the second half of that formula. I'm on a phone right now... so how about you give it a try (hint... use <>"" to see if not blank) and see what you get. I can help debug. Good luck

  • JMcGill
    JMcGill ✭✭

    @Scott Orsey, Thank you so much for sticking with me and giving me direction. This one worked!

    =COUNTIFS({Specific Sprint}, "Sprint 19 SD", {Components}, "Compliance", {Type}, HAS(@cell, "Story")) + COUNTIFS({Specific Sprint}, "Sprint 19 SD", {Components}, "Compliance", {Type}, HAS(@cell, "Task"), {Points}, <>"")

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭

    @JMcGill , Looks perfect. Glad to see you've got it working! Be well

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!