COUNTIF multiple criteria

bcrochet

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?

Ryan Sides

@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()))