Sumifs total from two sheets - Errors

Folks,

Looking to get a total from two sheets Vs a value in two cells and adding together for the same result. eg;

=SUMIFS({Sales Order Activation Range 5}, {Sales Order Activation Range 2}, Category@row) Gets me a result. and

=SUMIFS({Completed - Sales Order Activation's Range 4}, {Completed - Sales Order Activation's Range 4}, Category@row) also gets me a result. But when I look to combine with the below I get #Invalid Ref and other as I make iterations trying to counter the error.

=SUMIFS({Sales Order Activation Range 5}, {Sales Order Activation Range 2}, Category@row) + SUMIFS({Completed - Sales Order Activation's Range 4}, {Completed - Sales Order Activation's Range 4}, Category@row)

Appreciate any assistance.

Find more posts tagged with

Accepted answers

Hudson_Miller

@Jason P

I'm not seeing the problem with my mockup below. I have created one sheet called Model Prices and a second sheet called option prices.

The summary sheet has the criteria in the first two columns I want to search for, and a combined formula that reads:

=(SUMIFS({Model | Price}, {Model | Model}, Model@row)) + (SUMIFS({Option | Price}, {Option | Option}, Option@row))

Looking at your original post - I noted that the sum range and criteria range are the same "{Completed - Sales Order Activation's Range 4}". Is this correct or did you mean to sum a range using a different criteria column?

-Hud

All comments

AmandaWebb

Hi Jason,

Try this maybe

=SUMIFS({Sales Order Activation Range 5}, {Sales Order Activation Range 2}, Category@row, [{Completed - Sales Order Activation's Range 4}, {Completed - Sales Order Activation's Range 4}, Category@row])

Jason P

HI Amanda,

Hey thanks for getting back, gave it a try but no go. The use of [Square Brkts] is to reference a column on the same sheet. in may case I've created a metrics sheet that I want to pull the data into, so in their place these become the Range.

Hudson_Miller

@Jason P - I don't have the ability to mock up the sheets right now but one thing you can try is to place some some parenthesis on the outside of the SUMIFS to isolate them from the + as well as brackets around the second like.

=(SUMIFS({Sales Order Activation Range 5}, {Sales Order Activation Range 2}, Category@row)) + (SUMIFS({Completed - Sales Order Activation's Range 4}, {Completed - Sales Order Activation's Range 4}, Category@row))

Sorry I can't be certain this is, just a hunch.

Jason P

Hi @Hudson_Miller

Thanks for the suggestion, initially tried that too but no go. Little more reading and from what I can figure seems you cannot run Sums across two sheets.

Hudson_Miller

@Jason P

I'm not seeing the problem with my mockup below. I have created one sheet called Model Prices and a second sheet called option prices.

The summary sheet has the criteria in the first two columns I want to search for, and a combined formula that reads:

=(SUMIFS({Model | Price}, {Model | Model}, Model@row)) + (SUMIFS({Option | Price}, {Option | Option}, Option@row))

-Hud

Jason P

Hey Hudson.

You cracked it mate - thanks for that.

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.