sumif function across multiple sheets

I am trying to build a roll up sheet that pulls criteria from multiple sheets, into one master document. I keep having an issue when I update the formula range from one sheet, it updates all the ranges when I don't want it to do that.

Here is an example:

For each FFE column, I have a formula specific to each Item group (ie: duvet cover) . I want to total those up from each sheet (SEA FFE for example) into each specific row, and then copy that formula over to the next column (ie. BOS 2 FFE) and have it update the ranges. However, when I copy over the formula and update the ranges, it updates ALL of them on the sheet. It seems crazy that I would have to manually update each cell when i should just be able to update the range to find the next FFE sheet (MIN, KC, and beyond).

Formula example:

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @JennS_

Hope you are fine if i understand your question you create a formula to sum the item for example in sheet SEA FFE using the criteria in each row then you are coping the same formula to do the same for sheet BOS 2 FFE for the same criteria.

if this is the case then for the new sheet formula "BOS 2 FFE" don't use the command "Edit Referance" when you try to refer to the new sheet "BOS 2 FFE" because it's editing the same reference you used for the sheet "SEA FFE".

instead of that for sheet "BOS 2 FFE" keep the same formula but delete only the reference then add new referance:

the following is the sample formula:

for sheet SEA FFE = SUMIFS({SEA FFE}, HAS(@cell, "Roadshow"))

after you copy the formula to BOS 2 FFE = SUMIFS({BOS 2 FFE}, HAS(@cell, "Roadshow"))

JennS_

This did not work for me. Am I selecting the cell from BOS 2? Or am I referencing the sheet it is pulling from? It is giving me invalid reference. Note that each of these cells is pulling from a different sheet and summing up the cost.

JennS_

Bassam Khalil

@JennS_

If you can share me as an admin on a copy of your sheet after you remove any sensitive data I will create the exact formula for you then you can copy it to your original sheet

My Email: bassam.k@mobilproject.it

JennS_

Ok- I just shared it to you. Thank you!

Bassam Khalil

@JennS_

you only shared the main sheet i don't have access to the intake sheets to check your references if you can create a sample workspace and add 2 intake sheets and the summary sheet so we can check what exactly the problem you are facing.

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.