COUNTIFS with multiple criteria in same range and different ranges

✭✭

@Paul Newcome, would you be able to help me with a COUNTIFS formula that contains multiple criteria within the same range and within different ranges?

This count works with multiple criteria and multiple ranges, but now I need to add another criterion ("insect") within the same Metrics Range 3.

=COUNTIFS({Metrics Range 3}, "Bug", {Metrics Range 1}, "Race 18", {Metrics Range 5}, "Defect", {Metrics Range 6}, "Race 18 SD")

Thank you!

• ✭✭✭✭✭✭

You can have the same range twice, but when you have them as two separate pieces within the same COUNTIFS, you are basically saying "AND", so your formula is counting rows where it equals "this" and "that" at the same time which is not possible. Thus the reason for using the OR function with the syntax indicated in my last post.

• ✭✭✭✭✭✭

You would use an OR statement like so:

=COUNTIFS({Range}, OR(@cell = "this", @cell = "that"), ....................................................)

• ✭✭

thank you! I am so new at formulas so I will need to study up. I am referencing another datasheet so I don't think @cell will work? I have this so far that works but I need to add in {Type}, "Task". When I try it gives me a 0. Is it because you can't have two ranges that are the same?

=COUNTIFS({Sprints}, "Sprint 18", {Type}, "Story", {Components}, "Compliance")

• ✭✭✭✭✭✭

The @cell solution should work across sheets as well, so that would look like:

• =COUNTIFS({Sprints}, "Sprint 18", {Type}, OR(@cell = "Story", @cell = "Task"), {Components}, "Compliance")

If you are getting zeros I would check the ranges to make sure they refer to the correct data.\

Hope this helps!

Best,

Zach Hall

Training Delivery Manager / Charter Communications

• ✭✭✭✭✭✭

You can have the same range twice, but when you have them as two separate pieces within the same COUNTIFS, you are basically saying "AND", so your formula is counting rows where it equals "this" and "that" at the same time which is not possible. Thus the reason for using the OR function with the syntax indicated in my last post.

• ✭✭

Thank you, Paul! Do you know where there might be a video to help me with the @cell reference? Not sure I'm doing this correctly either.

• ✭✭✭✭✭✭

The @cell reference is just that. "@cell".

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!