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
-
Nick Korna ✭✭✭✭✭✭The formula from@Paul Newcomeshould 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
-
Nick Korna ✭✭✭✭✭✭
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!
-
Paul Newcome ✭✭✭✭✭✭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)))
-
Dan Benitah ✭✭
@Nick KornaThanks but i could not get it done... It shows #UNPARSEABLE for some reason
do you see something i don't?
Thanks again!
-
Nick Korna ✭✭✭✭✭✭
You've an extra set of brackets in the IFERROR:
If you remove these (the pink set) it should work OK.
-
Dan Benitah ✭✭
Thank you@Nick Korna!! it worked!
So if I want to push it by year like@Paul Newcomeis 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 bothso much for your help!!
-
Nick Korna ✭✭✭✭✭✭
The formula from@Paul Newcomeshould 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.
-
Dan Benitah ✭✭
Thank you guys very much, it worked!@Nick Kornaand@Paul Newcome
I AM ALL SET!!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//m.santa-greenland.com/community/discussion/107008/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
