SUMIFS function with Auto/Number system column

Steph Harper

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")

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:

=SUMIFS({Monthly Reporting & Deliverable Tracking Range 1}, {Monthly Reporting & Deliverable Tracking Range 2}, "Riparian Buffer Implementation (ac & ft)", {Monthly Reporting & Deliverable Tracking Range 18}, >DATE(2019, 6, 25), {Monthly Reporting & Deliverable Tracking Range 18}, <DATE(2020, 7, 15))

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

Paul Newcome

Try something like this...

=SUMIFS({Monthly Reporting & Deliverable Tracking Range 1}, {Monthly Reporting & Deliverable Tracking Range 2}, "Riparian Buffer Implementation (ac & ft)", {Monthly Reporting & Deliverable Tracking Range 17}, AND(DATEONLY(@cell) >= DATE(2020, 07, 15), DATEONLY(@cell) <= DATE(2020, 07, 29)))

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:

=SUMIFS({Monthly Reporting & Deliverable Tracking Range 1}, {Monthly Reporting & Deliverable Tracking Range 2}, "Riparian Buffer Implementation (ac & ft)", {Monthly Reporting & Deliverable Tracking Range 18}, >DATE(2019, 6, 25), {Monthly Reporting & Deliverable Tracking Range 18}, <DATE(2020, 7, 15))

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!