COUNT CELLS BY MONTH WITHIN ONE RANGE (COUNTIFS?)
Hello Everyone!
First time asking for help, I always found my answers within everyone else's questions, but not this time!
We work off one main sheet where all our leads come in, I would like to create in another sheet a count by month of that "created date" column, to see how many leads came in in January, in February, March...
What should be the formula to get my total by month?
I tried several ones but can't figure it out...
Thank you!!
Best Answer
-
The formula from @Paul Newcome should work, though the below would be an alternative:
=COUNTIFS({Date created Range 1}, AND(IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row), IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row)))
Either of these should work.
Answers
-
Hi @Dan Benitah,
A formula along the lines of this (using January as an example):
=COUNTIF({Date Created}, IFERROR(MONTH(@cell), 0) = 1)
You can alter this to be a column formula (using Start Date as the 2nd column) that will fill out the dates for you rather than needing to do them manually:
=COUNTIF({Date Created}, IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row))
Hope this helps - if you've any questions or comments then just post! 😊
-
I would suggest the below. It will allow for multiple years on the source sheet.
=COUNTIFS({Created Date Column}, AND(IFERROR(YEAR(DATEONLY(@cell)), 0) = YEAR([Month Start Date]@row), IFERROR(MONTH(DATEONLY(@cell)), 0) = MONTH([Month Start Date]@row)))
-
@Nick Korna Thanks but i could not get it done... It shows #UNPARSEABLE for some reason
do you see something i don't?
Thanks again!
-
You've an extra set of brackets in the IFERROR:
If you remove these (the pink set) it should work OK.
-
Thank you @Nick Korna !! it worked!
So if I want to push it by year like @Paul Newcome is proposing is there a more simple with AND function I can use to add the year? couldn't figure out the other one.
Thank you both so much for your help!!
-
The formula from @Paul Newcome should work, though the below would be an alternative:
=COUNTIFS({Date created Range 1}, AND(IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row), IFERROR(MONTH(@cell), 0) = MONTH([Start Date]@row)))
Either of these should work.
-
Thank you guys very much, it worked! @Nick Korna and @Paul Newcome
I AM ALL SET!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!