# COUNTIFS formula if name appears between given Dates

Do you know if there is a formula that will count how many times a name appears between given dates (10/1-12/31) in the Created (Date) column?

Thanks,

Senior Program Coordinator

De Anza College

• The OR function requires "logical statements" such as "this equals that" or "this is less than that". To accomplish this in the COUNTIFS, you would need to use @cell references.

=COUNTIFS({Name Range}, OR(@cell = "Name1", @cell = "Name2", @cell = "Name3"), {Date Created Range}, AND(IFERROR(MONTH(@cell), 0) >= 10, IFERROR(MONTH(@cell), 0) <= 11, IFERROR(YEAR(@cell), 0) = 2020))

• The last recommendation from @Paul Newcome fixed my issued and it worked perfectly again!! Thank you both so much 🤗

Senior Program Coordinator

De Anza College

• @Stacey Carrasco That gets just a little more complicated. My suggestion is a helper column on the source sheet that contains this formula...

=VALUE(YEAR(Date@row) + "" + IF(MONTH(Date@row)<10, "0") + "" + MONTH(Date@row))

This should give you a value of yyyymm on each row.

Now you can reference this new column in your formula and instead of specify a MONTH and YEAR, you would just use

AND(@cell >=202007, @cell <= 202106)

• Great! Happy to help. 👍️

• Try this:

=COUNTIF({Name Column Range}, [Name Column]@row, {Date Created Range}, AND(MONTH(@cell)>=10, MONTH(@cell)=<12))

Hope it helped!

• Thanks @David Joyeuse and @Paul Newcome that worked amazingly!!! Thank you both so much!!!

Senior Program Coordinator

De Anza College

• OK, I'm stumped again, this formula is working for this scenario but now I have another scenario that I want to count more then one name in the {range} but now I'm curious if in the range it can count more than 1 name. Is it a FIND command?

=COUNTIFS({Name Column Range}, "Name1", FIND("Name2")>0, {Date Created Range}, AND(IFERROR(MONTH(@cell), 0)>=10, IFERROR(MONTH(@cell),0)<=12, IFERROR(YEAR(@cell), 0) = 2020))

I know this formula is incorrect

Senior Program Coordinator

De Anza College

• Replace your name criteria with an AND function like:

AND("Name1", "Name2") if you want to look for both names in it.

If you look for either name, then use an OR:

OR("Name1", "Name2")

Example:

=COUNTIFS({Name Column Range}, AND("Name1", "Name2"), {Date Created Range}, AND(IFERROR(MONTH(@cell), 0)>=10, IFERROR(MONTH(@cell),0)<=12, IFERROR(YEAR(@cell), 0) = 2020))

• @David Joyeuse I'm receiving an Invalid Data Type

=COUNTIFS({Name Range}, OR("Name1", "Name2", "Name3"), {Date Created Range}, AND(IFERROR(MONTH(@cell), 0) >= 10, IFERROR(MONTH(@cell), 0) <= 11, IFERROR(YEAR(@cell), 0) = 2020))

I know I'm missing something but I can't seem to figure it out.

Senior Program Coordinator

De Anza College

• The OR function requires "logical statements" such as "this equals that" or "this is less than that". To accomplish this in the COUNTIFS, you would need to use @cell references.

=COUNTIFS({Name Range}, OR(@cell = "Name1", @cell = "Name2", @cell = "Name3"), {Date Created Range}, AND(IFERROR(MONTH(@cell), 0) >= 10, IFERROR(MONTH(@cell), 0) <= 11, IFERROR(YEAR(@cell), 0) = 2020))

• The last recommendation from @Paul Newcome fixed my issued and it worked perfectly again!! Thank you both so much 🤗

Senior Program Coordinator

De Anza College

• Since you help me so much on this last formula I now have an update to ask. Do you know how to search between the years starting from July 2020 and ending in June 2021?

Here's the scenario I have. I'm trying to search when the dept Prof. Development appears as long as the webinar has not been canceled in a check box and the session was between the dates of July 1, 2020 through June 20, 2021.

This formula is only from July through December because I'm not stumped how to continue to count from Jan. - June in one formula:

=COUNTIFS(Department:Department, "Professional Development", Canceled:Canceled, "0", Date:Date, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 12, IFERROR(YEAR(@cell), 0) = 2020))

Thanks so much!

Senior Program Coordinator

De Anza College

• @Stacey Carrasco That gets just a little more complicated. My suggestion is a helper column on the source sheet that contains this formula...

=VALUE(YEAR(Date@row) + "" + IF(MONTH(Date@row)<10, "0") + "" + MONTH(Date@row))

This should give you a value of yyyymm on each row.

Now you can reference this new column in your formula and instead of specify a MONTH and YEAR, you would just use

AND(@cell >=202007, @cell <= 202106)

• But it worked!!!

that's counting exactly what I needed given all my variables.

Thanks so much again!!

Senior Program Coordinator

De Anza College

• Great! Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!