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!
Best Answer
-
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.
Answers
-
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".
-
This content has been removed.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!