SUMIFS to Calculate Values for a Specific Year/Date Range

I am trying to use SUMIFS with multiple criteria, and I am failing horribly. I seem to be having trouble when I try to add the criterion to sum data from a specific year (from a Date field) and/or when I attempt to add data from a date range.

I have searched the community questions, and I haven't been successful in finding a solution. It is possible I haven't arrived at the correct post.

Thanks in advance for your assistance!

Find more posts tagged with

Sales

Sheets

Finance

Formulas

Reports

Accepted answers

Paul Newcome

You forgot the second DATE function.

You can also save yourself from having to edit two separate dates if you want to adjust which year you are summing for by using the method above where you use YEAR(@cell) = 2020 as your criteria.

All comments

Jennifer Whitlock

Hi Andrew,

Without knowing what other info you need in your formula, you should be able to pull the info relative to a specific year by inserting this into your SUMIFS statement as the range and criteria:

(Date:Date, >DATE(2020, 1, 1) < DATE(2020, 12, 31)

Would you share any existing progress in your formula to see if anyone can be more helpful?

Have a good one!

Andrew Brimer

Hi Jennifer,

Thank you for your input. I was able to get a bit closer to the solution, but I'm having difficulty now with the cross sheet formula and adding in an IFERROR function when the Date column is not populated or has "TBD" or "Other."

Thanks again for your quick response.

Paul Newcome

@Jennifer Amador Your criteria section will not work within Smartsheet. You would need to either use an AND statement with @cell references

Date:Date, AND(@cell > DATE(2020, 1, 1), @cell < DATE(2020, 12, 31))

or you would need to repeat the range.

Date:Date, > DATE(2020, 1, 1), Date:Date, < DATE(2020, 12, 31)

@Andrew Brimer Try this for your criteria...

IFERROR(YEAR(@cell), 0) = 2020

Andrew Brimer

@Paul Newcome and @Jennifer Amador

Here's my current formula which is #UNPARSEABLE:

=SUMIFS({Data Range to Sum}, {Data_Check Column}, =0, {Data_A Exp Date}, AND(@cell>=DATE(2020, 1, 1), @cell<=(2020,12, 31)))

Thank you for your assistance.

Paul Newcome

You forgot the second DATE function.

You can also save yourself from having to edit two separate dates if you want to adjust which year you are summing for by using the method above where you use YEAR(@cell) = 2020 as your criteria.

Andrew Brimer

@Paul Newcome Thank you for catching my error! The formula is now working.

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.