Countifs with multiple criteria

JMcGill

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")

Scott Orsey

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

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

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

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

@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

@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

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