Sumifs with cross sheet reference and date criteria

Nicole C · 01/27/22

I am trying to create a formula on a separate sheet that adds up the data from the sheet shown below for quantity of chairs, "Chair 2 Qty.", that are a specific type/name from column, "Chair 2", and have a construction start date, "Construction Onsite", that is in the future.

I have it working without the date reference, but now I need to add in the date part.

Here's what has worked so far: =SUMIF({Chair 2}, [Primary Column]@row, {Chair 2 Qty.})

Here's the date portion that isn't working: =SUMIFS({Construction Onsite}, >TODAY(),{Chair 2}, [Primary Column]@row, {Chair 2 Qty.})

I get "Invalid Operation". Please help :)

Nicole C · 01/27/22

I figured it out without having to add a new column to my original sheet:

=SUMIFS({Chair 2 Qty.}, {Construction Onsite}, >=DATE(2022, 1, 1), {Construction Onsite}, <=DATE(2022, 12, 31), {Chair 2}, [Primary Column]@row)

Thanks for your help!

Nicole C · 01/27/22

Also...how would the date criteria differ if I want to show the quantity for anything in construction during 2022?

Jeff Reisman · 01/27/22

It's kind of unintuitive, but the syntax changes between SUMIF and SUMIFS. In SUMIF, if your range to add up is different than the criteria range, it is listed AFTER the criteria range. In SUMIFS, the range to add up is always listed FIRST.

=SUMIFS({Chair 2 Qty.}, {Construction Onsite}, >TODAY(), {Chair 2}, [Primary Column]@row)

For your second question, to only show construction dates in 2022:

=SUMIFS({Chair 2 Qty.}, {Construction Onsite}, YEAR(@cell) = 2022, {Chair 2}, [Primary Column]@row)

Nicole C · 01/27/22

Ah makes sense now! The first formula works, but the second doesn't, I think because my date column is showing dates versus years. Is there a way to use a date criteria that reflects 01/01/22 through 12/31/22?

Jeff Reisman · 01/27/22

https://community.smartsheet.com/discussion/comment/317586#Comment_317586

Odd that the second formula is not working for you. I built the same thing into a couple of test sheets:

=SUMIFS({New Test Sheet Screening}, {New Test Sheet VisitDate}, YEAR(@cell) = 2022, {New Test Sheet type}, Type@row)

and it works for me, even if I set my value being added on the remote sheet to be a calculated formula value.

The YEAR(@cell) = 2022 uses the YEAR function to find dates with that value for the year. YEAR automatically returns the 4-digit year value from a date field.

Nicole C · 01/27/22

I figured it out without having to add a new column to my original sheet:

=SUMIFS({Chair 2 Qty.}, {Construction Onsite}, >=DATE(2022, 1, 1), {Construction Onsite}, <=DATE(2022, 12, 31), {Chair 2}, [Primary Column]@row)

Thanks for your help!

Nicole C · 01/27/22

Can I create the same formula but instead of using three unique cross-sheet references, create one cross-sheet reference that includes several columns and then reference the specific column within the formula? I'm worried about the limit of unique cross-sheet references.

This is the one that works with unique column references:

=SUMIFS({Chair 2 Qty.}, {Construction Onsite}, >=DATE(2022, 1, 1), {Construction Onsite}, <=DATE(2022, 12, 31), {Chair 2}, [Primary Column]@row)

This is what I tried to do with one reference for Chair type (column 1 in the reference range) and Chair Qty (column 2 in the reference range), but it didn't work:

=SUMIFS({MOD Furniture & Lighting per Store Range 1}, 2, {Construction Onsite}, >=DATE(2022, 1, 1), {Construction Onsite}, <=DATE(2022, 12, 31), {MOD Furniture & Lighting per Store Range 1}, 1, [Primary Column]@row)

Sumifs with cross sheet reference and date criteria

Best Answer

Answers

Help Article Resources

Categories