SUMIFS/COUNTIFS Date Issue

Hi all,

I am attempting to create a formula to add up all the Employees from a sheet from last month. This sheet is pulling in totals on an automation monthly. The formula works except the Month section keeps making it 0 when I see it should be a 1. I have a similar issue with a COUNTIF formula.

Can someone tell me the correct way to write this month Piece so it only pulls totals from last month and then how I would right it to pull totals from this month?

The {Historic Month} and the {Term from Roster} columns are Date Only.

=SUMIFS({Historic Totals}, {Historic Month}, MONTH(TODAY(-1)), {Historic Markets}, Market@row - TOTAL@row)

=COUNTIFS({Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row, {Term from Roster}, =MONTH(TODAY()))

Thank you!

Find more posts tagged with

Formulas

Accepted answers

Christian G.

It seems that you compare a whole date vs a month

{Term from Roster} is a date

MONTH(TODAY())-1 is a number from 0 to 11

You should compare MONTH({Term from Roster}) to MONTH(TODAY()-1)

Also, MONTH(TODAY(-1)) is yesterday's month. not last month.

Also, make sur you catch the case Month(Today())-1 =0

Try this:

=SUMIFS({Historic Totals}, Month({Historic Month}), if(MONTH(TODAY()-1))=0,12,MONTH(TODAY()-1))), {Historic Markets}, Market@row - TOTAL@row)

=COUNTIFS({Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row, Month({Term from Roster}), =MONTH(TODAY()))

All comments

Christian G.

It seems that you compare a whole date vs a month

{Term from Roster} is a date

MONTH(TODAY())-1 is a number from 0 to 11

You should compare MONTH({Term from Roster}) to MONTH(TODAY()-1)

Also, MONTH(TODAY(-1)) is yesterday's month. not last month.

Also, make sur you catch the case Month(Today())-1 =0

Try this:

=SUMIFS({Historic Totals}, Month({Historic Month}), if(MONTH(TODAY()-1))=0,12,MONTH(TODAY()-1))), {Historic Markets}, Market@row - TOTAL@row)

=COUNTIFS({Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row, Month({Term from Roster}), =MONTH(TODAY()))

Dakota Haeffner

@Christian G. Thank you for the assistance! Your formula helped get me almost all the way there but I needed to account for the previous year being looked at in certain instances for Janurary.

Final Formulas ended up being this..

=TOTAL@row - IF(MONTH(TODAY()) = 1, SUMIFS({Historic Totals}, {Historic Month}, IFERROR(MONTH(@cell), 0) = 12, {Historic Month}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1, {Historic Markets}, Market@row), SUMIFS({Historic Totals}, {Historic Month}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Historic Month}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Historic Markets}, Market@row))

=IF(MONTH(TODAY()) = 1, COUNTIFS({Term Create Date}, IFERROR(MONTH(@cell), 0) = 12, {Term Create Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1, {Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row), COUNTIFS({Term Create Date}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Term Create Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Terminated ES}, "Promoted/Transfer", {Terminated Market}, Market@row))

Christian G.

@Dakota Haeffner Great ! congratulation on finding the right syntaxt. I'm glad I could 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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.