COUNTIFS formula if name appears between given Dates
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,
Senior Program Coordinator
De Anza College
Best Answers
-
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))
-
Thanks @Paul Newcome for cerrecting me.. Was obviously not awaken enough...
-
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)
-
Answers
-
Try this:
=COUNTIF({Name Column Range}, [Name Column]@row, {Date Created Range}, AND(MONTH(@cell)>=10, MONTH(@cell)=<12))
Hope it helped!
-
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))
-
Thanks @Paul Newcome for cerrecting me.. Was obviously not awaken enough...
-
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
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 75 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!