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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!