SUMIFS function with Auto/Number system column

Hi all! I'm trying to summarize deliverables using the date stamp of when a form was submitted. Unfortunately the way our form is set up, we have employees fill out a form and enter the month and the year separately so our sheet does not have a normal date column. Is this possible to use the Auto/Number system column as a traditional date in a sumifs formula? The equation I'm using doesn't come back with the UNPARSEABLE but it is saying the sum is 0 when I know there are actual numbers that have been reported. Also, this is an equation referencing another smartsheet, which is why it says Range 1 instead of the column name. Thanks for any and all help!

=SUMIFS({Monthly Reporting & Deliverable Tracking Range 1}, {Monthly Reporting & Deliverable Tracking Range 2}, "Riparian Buffer Implementation (ac & ft)", {Monthly Reporting & Deliverable Tracking Range 17}, "07/29/19 12:00 AM - 07/15/20 12:00 AM")

Find more posts tagged with

Formulas

Accepted answers

Steph Harper

If i use that, now I get UNPARSEABLE. But I did figure it out with a little help from your answer. First, I did a bit of a work around to reduce the size of my formula and made a new column with date only since I don't care about the time. Then I found out that AND statements can't be used with SUMIFS. The dates are off but here is what worked for me:

Thanks for your answer! It definitely helped me put the dates in correctly.

All comments

Paul Newcome

Try something like this...

Steph Harper

Thanks for your answer! It definitely helped me put the dates in correctly.

Paul Newcome

AND functions can be used in SUMIFS. I use it very frequently. SUMIFS, COUNTIFS, COLLECT... They all accept the AND function.

I'm not sure what the reason is for the #UNPARSEABLE error. Commas and parenthesis and whatnot are all in the correct placement in the formula I provided.

As for the dates being off... My apologies. I was using the dates in the formula in your original post (except I missed the 2019 vs 2020).

Glad you were able to get something working though.

Steph Harper

Hmm I'll have to play around with the AND statements a little more to try to get them to work. Oh and no worries, I changes the dates. They are kind of arbitrary in a sense since we report at the beginning of the month for the last month and since I'm using a form submission I have to give employees enough time to fill out the form so I can capture the true reporting of the fiscal year. It's weird.

Thanks again for your quick response!

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.