SUMIFS with Data in another sheet

Hello - I need help with the following. I created a summary sheet with goals by Functional Area (Department). I want to add 2023 actuals (to compare the actuals with the 2023 targets).

The actuals information is stored in another sheet. I am thinking about using SUMIFS. Here are my 3 pieces of information:

The range to sum is called [FY '23] - in the sparate sheet
The first criteria is in a column called "Type" - in the separate sheet
The second criteria is in a second column called [Functional Area / Dpt] - in the separate sheet

Can you please help me write my formula? This is what I have so far and it is not working:

=SUMIFS({2023 PPI & PMO Tracker Range 1},Type:Type,"2022 Carry Over",[Functional Area / Dpt]:[Functional Area / Dpt],FEU")

Thanks in advance for your help.

Find more posts tagged with

Accepted answers

All comments

Samuel Mueller

@Regis Thermo you have to use a cross sheet reference for each column in the separate sheet. It would be more like

=SUMIFS({2023 PPI & PMO Tracker Range 1}, {type}, "2022 Carry Over", {Functional Area} ,"FEU")

{2023 PPI & PMO Tracker Range 1} is the cross sheet reference column you want to sum in the separate sheet

{type} is a cross sheet reference to the type column in the separate sheet.

{Functional Area} is a cross sheet reference to the functional area column in the separate sheet

Regis Thermo

Samuel - thanks a bunch for your prompt answer but I am not there yet. Here is what I typed:

=SUMIFS({2023}, {Type}, "2022 Carry Over", {Functional Area}, "MFG")

where:

{2023} is the reference separate sheet - I do not know why the initial name {2023 PPI & PMO Tracker Range 1} is truncated!
{Type} and {Functional Area} are the names of columns in the separate sheet (these column are not referenced as I will not SUM data in these columns)

What should I do not to get #INVALID REF error message? Thanks in advance for your help. Kind regards,

🙄

Samuel Mueller

@Regis Thermo you have to create cross sheet references and use the name of reference you created, do you know how to do that?

I do not know what your reference names are I just sent an example.

Your criteria ranges have to be from the same sheet as the range you are trying to sum, but your criteria can be on your current sheet

You need 3 referenced ranges

{2023 PPI & PMO Tracker Range 1} - the range you are summing on the separate sheet

{2023 PPI & PMO Tracker Range type 1} - the range that has the types in it on the separate sheet

{2023 PPI & PMO Tracker Range functional area 1} - the range that has the functional area on thesummary sheet

I do know know what the actual name of the reference will be when you create it though, just an example

Regis Thermo

Thanks Samuel! Follow-up questions:

1) How do I creacte multiple references?

2) How to insert each of these ranges in the SUMIFS formula

Thanks so much for the support.

Kindn regards,

Regis

Samuel Mueller

@Regis Thermo you need to right click on a cell and choose manage references

You'll get a popup called sheet reference manager, then you will hit "create"

After you hit create a new popup will show up where you will search for your sheet you want to reference

Find the column you need to reference, select the column header, then hit "insert reference". Remember the "sheet reference Name" or name it something you can easily remember before you hit "Insert Reference"

repeat this process for every cross sheet reference or column you need to reference on the other sheet.

Regis Thermo

Thanks a bunch Samuel. Have a great weekend. Kind regards

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.