Sumifs with cross sheet reference and date criteria

Options

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


Best Answer

  • Nicole C
    Nicole C ✭✭
    Answer ✓
    Options

    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!

Answers

  • Nicole C
    Options

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/27/22
    Options

    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)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Nicole C
    Options

    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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Nicole C
    Nicole C ✭✭
    Answer ✓
    Options

    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
    Options

    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!