Sumif across sheets using criteria

=SUMIFS({New product Load sku}, {New Product- Prime Tab}, [Primary Column]@row, {New Load- Stage}, OR(@cell <> "Ops Team Reviewing", @cell <> "Working", @cell <> "Assign to Data Team", @cell <> "Data Team Rejected", @cell <> "Ops Team Missing Info Request Sent", @cell <> "DT Reviewed - Ready To Assign", @cell <> "With PM", @cell <> "Haresh Results Ready", @cell <> "Sent Overseas"))


trying to sum skus from this sheet using Web Prime but Data Stage should be one of the above...im missing something here


Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 05/25/21

    Hi @Alexis Taggard

    Hope you are fine, could you please share me as an admin on a copy of the 2 sheets (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • shared a copy with you!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Alexis Taggard 

    Ok i will check it and come back to you ASAP

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi @Alexis Taggard

    It looks like you're searching for criteria in a Multi-Select column which is likely why you're having issues.

    The way your current formula is set up, it will only SUM values if the Prime Tab is equal to the exact content in the Primary Column. This means if your Primary says "Electrical", it will only SUM where "Electrical" is selected on its own, and it will ignore any cells where "Electrical" is also with "Office", etc.

    There's a function called HAS which is specifically designed for Multi-Select columns. It can look to see if a cell has a certain value along with other criteria (or on its own).

    Try this:

    =SUMIFS({New product Load sku}, {New Product- Prime Tab}, HAS(@cell, [Primary Column]@row), {New Load- Stage}, OR(@cell <> "Ops Team Reviewing", @cell <> "Working", @cell <> "Assign to Data Team", @cell <> "Data Team Rejected", @cell <> "Ops Team Missing Info Request Sent", @cell <> "DT Reviewed - Ready To Assign", @cell <> "With PM", @cell <> "Haresh Results Ready", @cell <> "Sent Overseas"))


    Let me know if this works!

    Cheers,

    Genevieve

  • hmm still seems to be ignoring the Status so Office for instance is returning a total number of skus where status is not one of the above

  • Hi @Alexis Taggard

    Can you clarify what it is you're looking for in the Status column? Which I presume is the  {New Load- Stage} range?

    <> indicates "not", so your formula is looking for rows where your row is not "Ops Team Reviewing", etc.

    Are you looking for where it is this criteria? Try swapping all your <> for = instead.

  • yup that was it!....clearly i need some more coffee. thank you so much!

  • Haha no problem! I'm glad we figured it out. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!