Sum of Totals by area and Date

I am trying to set up a formula that does the following:

If in Work Log A, the Plant is X and the date range is between 10-1-2020 and now, what is the total cost of this time range for this sheet?

I also want to break down by monthly range:

If in Work Log A the plant is X and the date range is between 1-1-2021 and 1-31-2021, what is the total cost of this time range for this sheet?

I have similar formulas for the references in the other sheets, but what's hanging me up is trying to specify a date range within the formula.

Find more posts tagged with

Sheets

Formulas

Cell linking

Accepted answers

Paul Newcome

No. The @cell is as it should be. It looks like you have a period after the first @cell reference though. Try removing that. You are also going to want GREATER THAN or equal to for your first date argument, but you only have equal to.

All comments

Paul Newcome

Try something along the lines of...

=SUMIFS({Source Sheet Column To Sum}, {Source Sheet Plant Column}, "Plant X", {Source Sheet Date Column}, AND(@cell >= DATE(2020, 10, 01), @cell <=TODAY()))

EDITED to correct a typo in the formula

Walter Mootz

I got an Unparseable error for the formula with that formula. Here is a screenshot of it:

Do i need to put a specific cell reference for @cell? if so, what should i do when i want the total of everything that meets the date and plant criteria?

Paul Newcome

Walter Mootz

https://community.smartsheet.com/discussion/comment/272709#Comment_272709

That solved it, i must not have hit shift on the period to get the greater than symbol. Thank you so much for the help!!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.