COUNTIF by Month in Date

I've always struggled with calculating total count by Month; i can do it for every else but when you throw in dates, it throws me right off.

Essentially what i am trying to do is calculate on another sheet the amount of travel done within the month of March

This is the formula i am using, but it comes back with 0

=COUNTIFS({Middle East Crisis Range 3}, CONTAINS("March", @cell ))

This is the column that i am trying to pull the "March" from

What on gods green planet am i doing wrong?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

If that is set as a date type column, the "March" that you see is not actually what is used on the "back-end" for calculations. It uses the month number for that.

Try this:

=COUNTIFS({Middle East Crisis Range 3}, IFERROR(MONTH(@cell), 0) = 3)

All comments

AngB789

I have never had luck with that date format but I have when I change that date column formula to just numbers then you can just use a helper column Month Number Helper and Month Name. You would then be able to do the count formula to count the number of March.

Month Number Helper: =MONTH([Travel Start]@row)

Month Name: =IF([Month Number Helper]@row = 1, "January", IF([Month Number Helper]@row = 2, "February", IF([Month Number Helper]@row = 3, "March", IF([Month Number Helper]@row = 4, "April", IF([Month Number Helper]@row = 5, "May", IF([Month Number Helper]@row = 6, "June", IF([Month Number Helper]@row = 7, "July", IF([Month Number Helper]@row = 8, "August", IF([Month Number Helper]@row = 9, "September", IF([Month Number Helper]@row = 10, "October", IF([Month Number Helper]@row = 11, "November", IF([Month Number Helper]@row = 12, "December", ""))))))))))))

Paul Newcome

If that is set as a date type column, the "March" that you see is not actually what is used on the "back-end" for calculations. It uses the month number for that.

Try this:

=COUNTIFS({Middle East Crisis Range 3}, IFERROR(MONTH(@cell), 0) = 3)

Cayla Davis

my hero! THANK YOU!!! 🤩

Paul Newcome

Happy to help. 👍️

Paul Newcome

@AngB789 Using the helper column to output the month text can also be very helpful, but you can save yourself a column by nesting the MONTH function directly into your nested IF.

=IF(MONTH([Date Column]@row) = 1, "January", IF(MONTH([Date Column]@row) = 2, "February", IF(………….

I also have a shorter formula that I like to use if I only need the first three characters of the month.

=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (MONTH([Date Column]@row) * 3) - 2, 3)

I have a longer one for full month names as well if you're interested in that.

Cayla Davis

I need you on speed dial 😆

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.