Formula Help: Sumifs, based on multiple values and excluding one value

I'm struggling with my Sumifs formula, where I want to return a sum value based on two conditions and a further third condition that excludes a certain condition.

I can get the first bit working, but I'm struggling to exclude the status 'Cancelled' from the calculation.

Can anyone help advise where I'm going wrong please? The formula references another smartsheet.

=SUMIFS({LOG | Purchase Orders Range 3}, {LOG | Purchase Orders Range 4}, [Project Number & Name]$1, {LOG | Purchase Orders Range 5}, Item@row , <>"Cancelled", {LOG | Purchase Orders Range 7}, "Cancelled")

Thanks

Find more posts tagged with

Accepted answers

DKazatsky2

Hi @EllaSP,

You were very close - give this a try.

=SUMIFS({Range to be summed from Input}, {Project Number & Name from Input}, [Project Number & Name]$1, {Category from Input}, Category@row , {PO Status from Input}, <>"Cancelled")

Make sure to change the ranges to what you are using.

Hope this helps,

Dave

All comments

Naeem Ejaz

You can create 3 separate reports and use reports SUM feature.

EllaSP

Thanks Naeem, but unfortunately that won't work in this case. It needs to be within the one sheet, so I'm trying to get the right formula to work.

DKazatsky2

Hi @EllaSP,

The formula as written has syntax errors. Would it be possible for you to share a screenshot of the sheet, masking sensitive data? If you could be a little more specific in what is needed it would be helpful.

This part of your formula seems to not have anything associated with it: <>"Cancelled"

Thanks.

Dave

EllaSP

Hi Dave,

I have a summary sheet called 'Sheet 1' shown below. In the 'Receipted So Far' column I want to create the sum formula based on data in another sheet called 'Input'.

Sheet 1:

The sum value is based on input data that is going into 'Input'. The condition to sum is:

the project name should match the 'Project Number & Name' from Sheet 1
and it should also match the category according to 'Item' in Sheet 1
but it should exclude values when the PO Status is 'Cancelled' because this purchase has been cancelled and therefore no money is be added to Sheet 1.

Let me know if you need any more information or screenshots. I managed to get the formula to work up to the point of trying to exclude the condition relating to the PO Status = Cancelled.

DKazatsky2

Hi @EllaSP,

You were very close - give this a try.

=SUMIFS({Range to be summed from Input}, {Project Number & Name from Input}, [Project Number & Name]$1, {Category from Input}, Category@row , {PO Status from Input}, <>"Cancelled")

Make sure to change the ranges to what you are using.

Hope this helps,

Dave

EllaSP

https://community.smartsheet.com/discussion/comment/488801#Comment_488801

Hi Dave,

That worked, thank you very much for your help :)

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.