COUNTIFS formula if name appears between given Dates

Stacey Carrasco

Hi, @Paul Newcome

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,

Paul Newcome

The only thing I personally would do differently from @David Joyeuse would be to include IFERROR statements (personal preference) and a year. Also need to make it a countifS to include multiple range/criteria sets. And almost missed that the last argument needs the operators flipped around <= vs =<).

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

David Joyeuse

Thanks @Paul Newcome for cerrecting me.. Was obviously not awaken enough...

Paul Newcome

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

Stacey Carrasco

@Paul Newcome @David Joyeuse

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

Paul Newcome

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

Paul Newcome

Great! Happy to help. 👍️

David Joyeuse

Hi @Stacey Carrasco

Try this:

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

Hope it helped!

Paul Newcome

The only thing I personally would do differently from @David Joyeuse would be to include IFERROR statements (personal preference) and a year. Also need to make it a countifS to include multiple range/criteria sets. And almost missed that the last argument needs the operators flipped around <= vs =<).

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

David Joyeuse

Thanks @Paul Newcome for cerrecting me.. Was obviously not awaken enough...

Stacey Carrasco

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

Stacey Carrasco

@David Joyeuse, @Paul Newcome

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

David Joyeuse

@Stacey Carrasco

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

Stacey Carrasco

@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.

Paul Newcome

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

Stacey Carrasco

@Paul Newcome @David Joyeuse

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

Stacey Carrasco

@Paul Newcome

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!

Paul Newcome

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

Stacey Carrasco

@Paul Newcome

But it worked!!!

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

Thanks so much again!!

Paul Newcome

Great! Happy to help. 👍️