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

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

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.

Alexis Taggard

shared a copy with you!

Bassam Khalil

@Alexis Taggard

Ok i will check it and come back to you ASAP

Alexis Taggard

any luck here?

Genevieve P.

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

Alexis Taggard

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

Genevieve P.

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.

Alexis Taggard

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

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.