Can I nest more than one SUMIF

I am trying to create a SUMIF that will evaluate three options from drop down column and return a reduced percentage sum from within another column on the same sheet.

Drop down Column - Affected Customer Impact Risk

Options - Low Impact Risk (25%), Moderate Impact Risk (50%) or High Impact Risk (90%)

Take the total amount from the # of Accounts Affected and return a reduced value total.

=SUMIF([Affected Customer Impact Risk]@row, ="Low Impact Risk (25%)", [# of Accounts Affected]@row * 0.25, SUMIF([Affected Customer Impact Risk]@row, ="Moderate Impact Risk (50%)", [# of Accounts Affected]@row * 0.5, =SUMIF([Affected Customer Impact Risk]@row, ="High Impact Risk (90%)", [# of Accounts Affected]@row * 0.9)))

I have tried using the SUMIFS as well as an OR operator but it will not calculate more than one at a time.

Find more posts tagged with

Accepted answers

All comments

Mark Cronk

Hi @Witherwax ,

Try:

=(SUMIF([# of Accounts Affected]:[# of Accounts Affected], [Affected Customer Impact Risk]:[Affected Customer Impact Risk], @cell="Low Impact Risk (25%)")* 0.25)+(SUMIF([# of Accounts Affected]:[# of Accounts Affected], [Affected Customer Impact Risk]:[Affected Customer Impact Risk], @cell="Moderate Impact Risk (50%)")* 0.5)+(SUMIF([# of Accounts Affected]:[# of Accounts Affected], [Affected Customer Impact Risk]:[Affected Customer Impact Risk], @cell="High Impact Risk (90%)")* 0.9)

Work?

Mark

Witherwax

It is still giving me an #INVALIDOPERATION Error with this formula.

Paul Newcome

@Mark Cronk You used the SUMIFS syntax with a SUMIF function.

@Witherwax Give this a go...

=(SUMIFS([# of Accounts Affected]:[# of Accounts Affected], [Affected Customer Impact Risk]:[Affected Customer Impact Risk], @cell="Low Impact Risk (25%)")* 0.25)+(SUMIFS([# of Accounts Affected]:[# of Accounts Affected], [Affected Customer Impact Risk]:[Affected Customer Impact Risk], @cell="Moderate Impact Risk (50%)")* 0.5)+(SUMIFS([# of Accounts Affected]:[# of Accounts Affected], [Affected Customer Impact Risk]:[Affected Customer Impact Risk], @cell="High Impact Risk (90%)")* 0.9)

Mark Cronk

Thanks @Paul Newcome !

Mark

Paul Newcome

@Mark Cronk Haha. No worries. I've done it quite a few times and then spent way more time than I should have looking everywhere else for the problem.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.