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

Screen Shot 2023-06-28 at 8.35.55 AM.png

Screen Shot 2023-06-28 at 8.36.07 AM.png

What should be the formula to get my total by month?

I tried several ones but can't figure it out...

Thank you!!

Find more posts tagged with

Accepted answers

Nick Korna

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.

All comments

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 Korna Thanks but i could not get it done... It shows #UNPARSEABLE for some reason

do you see something i don't?

Thanks again!

Screen Shot 2023-06-28 at 9.25.22 AM.png

Screen Shot 2023-06-28 at 9.26.10 AM.png

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

Screen Shot 2023-06-28 at 9.57.15 AM.png

Nick Korna

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.

Dan Benitah

Thank you guys very much, it worked! @Nick Korna and @Paul Newcome

I AM ALL SET!!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.