COUNTIF multiple criteria
I have two different data sets. One running over a year and one month to date.
Some individuals have multiple entries with but used different names. For example John Doe and Johnny Doe. I need to count both as one so my formula for all data entry (which works) is:
=COUNTIF({RangeName}, "John Doe") + COUNTIF({RangeName}, "Johnny Doe")
Now I need to do the same for month to date. My formula (which works for one name) is:
=COUNTIFS({RangeName}, "John Doe", {RangeDate}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
How do I add Johnny Doe to the second formula in order to count both names?
Answers
-
@bcrochet Good morning!
In your second formula where you're looking for "John Doe", you can use the OR function with either HAS or CONTAINS
The HAS function will look for the cell to equal the entire string "John Doe"
The CONTAINS function will look for the cell to have some portion of the string "John Doe" in it. (You could use "John" in this case and it would count both "John Doe" and "Johnny Doe" b/c they both have "John" in them.
So your second formula if you want to use HAS:
=COUNTIFS({RangeName}, OR(HAS(@cell, "John Doe"), HAS(@cell, "Johnny Doe")), {RangeDate}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!