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

  • Ryan Sides
    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()))

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!