Counting occurrences for each month for this year only

I am using the formula below for a trend widget that pulls metrics for each month. This one is for January, but I have the same formula for each month, replacing 1 for january with 2 for february, etc. I need to update this formula so it counts if the month is january and the year is this year. I pulled one of my YTD formula from another sheet and threw a "hail mary" combining them. Any one know how to fix this?

Original working formula for the month of January:

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(MONTH(@cell ), 0) = 1)

My attempt to frankenstein a YTD version :/

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFFERROR (YEAR(@cell), 0) = YEAR(TODAY()), {Sourced Date}, IFERROR(MONTH(@cell ), 0) = 1)

Find more posts tagged with

Accepted answers

Nick Korna

Hi @Erica Cole,

What error is your formula giving? Your formula should work (the month/year order doesn't matter) so either of the below should be valid:

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1)

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1, {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

My initial thought was that the Sourced Date column hadn't been set to date, but this would stop the original formula from working. The only other thing I can think of is if the cross sheet references (if this is a different sheet) aren't set up quite right (one being a column and the other only a selection of cells?), but the error you're getting might shed some more light on this.

All comments

Nick Korna

Hi @Erica Cole,

What error is your formula giving? Your formula should work (the month/year order doesn't matter) so either of the below should be valid:

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1)

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1, {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Erica Cole

Hi Nick,

Thank you for responding. I'm getting #UNPARSEABLE with the first formula. But the second formula is working. I had tried it that way, but when it worked, I thought the count was off. Now that I'm manually checking the numbers, I think it's accurate. Thanks!

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.