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
-
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.
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!
-
Ok i will check it and come back to you ASAP
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"
-
any luck here?
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!