SUMIFS Help - Calculate a range based on current month

Hello!

I'm looking for a formula that I can use to calculate a range based on current month.

Attached is a screenshot of what I'm working with.

I want the YTD column to provide a total sum based on what month it is currently. Example - We are currently in march, so YTD would show the sum of Jan, Feb, and March so far.

When we move into April, it will also add that to the total.

I hope that makes sense for what I'm trying to do.

Any help would be greatly appreciated!

Thanks!

Find more posts tagged with

Accepted answers

Todd M Keller

This assumes that your data is in row "1", and your Month columns are labelled the same way as they are in your example....

=IF(MONTH(TODAY()) = 1, Jan1, IF(MONTH(TODAY()) = 2, SUM(Jan1:Feb1), IF(MONTH(TODAY()) = 3, SUM(Jan1:Mar1), IF(MONTH(TODAY()) = 4, SUM(Jan1:Apr1), IF(MONTH(TODAY()) = 5, SUM(Jan1:May1), IF(MONTH(TODAY()) = 6, SUM(Jan1:Jun1), IF(MONTH(TODAY()) = 7, SUM(Jan1:Jul1), IF(MONTH(TODAY()) = 8, SUM(Jan1:Aug1), IF(MONTH(TODAY()) = 9, SUM(Jan1:Sep1), IF(MONTH(TODAY()) = 10, SUM(Jan1:Oct1), IF(MONTH(TODAY()) = 11, SUM(Jan1:Nov1), IF(MONTH(TODAY()) = 12, SUM(Jan1:Dec1)))))))))))))

All comments

Eric Law

Hello @Luke W

I think you need to change where things are a bit, and you can do the YTD as a summary or in a different cell, up to you. Here is what I would do and the formula used "=SUMIF(MonthN:MonthN, <=MONTH(TODAY()), ValueV:ValueV)"

Todd M Keller

This assumes that your data is in row "1", and your Month columns are labelled the same way as they are in your example....

Luke W.

Thank you both for your help. @Todd M Keller - That did the trick!

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.