COUNTIFS with multiple criteria in same range and different ranges

JMcGill

@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!

Paul Newcome

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.

Paul Newcome

You would use an OR statement like so:

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

JMcGill

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

Zachary Hall

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!

Paul Newcome

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.

JMcGill

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.

Paul Newcome

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

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell