SUMIFS using criteria in rows and columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1866990%22%20slang%3D%22en-US%22%3ESUMIFS%20using%20criteria%20in%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1866990%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20I%20am%20trying%20to%20create%20a%20%22consolidating%22%20income%20statement.%20I%20am%20trying%20to%20do%20this%20via%20the%20'SUMIFS'%20function%20as%20I%20have%20two%20criteria.%20The%20criteria%20are%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20the%20month%20on%20row%202%20of%20'Income%20Statement%202'%20tab%20agrees%20to%20the%20respective%20date%20(row%204)%20on%20the%20Consolidating%20tab%3C%2FP%3E%3CP%3E2)%20the%20%22Consol%20PL%20ref%22%20in%20column%20B%20of%20'Income%20Statement%202'%20tab%20agrees%20to%20the%20respective%20income%20statement%20caption%20in%20column%20C%20of%20of%20the%20'Consolidating'%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20there%20are%20two%20criteria%2C%20I've%20tried%20to%20use%20a%20SUMIFS%20in%20cell%20E7%20but%20can't%20figure%20out%20what%20the%20issue%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1866990%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1867205%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIFS%20using%20criteria%20in%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867205%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827443%22%20target%3D%22_blank%22%3E%40egspen2%3C%2FA%3E%26nbsp%3BUse%20INDEX%2FMATCH%20instead.%20Demonstrated%20it%20in%20your%20attached%20file%20with%20the%20help%20of%20some%20named%20ranges%20to%20make%20the%20formula%20easier%20to%20read%2Fmaintain.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1867207%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIFS%20using%20criteria%20in%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThanks.%20I%20was%20curious%20if%20Index%2BMatch%2BMatch%20was%20going%20to%20be%20the%20response.%20Do%20I%20correctly%20infer%20then%20that%20SUMIFS%20is%20not%20able%20to%20do%20what%20I%20was%20trying%20to%20do%3F%20If%20so%2C%20can%20you%20briefly%20explain%20why%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1867256%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIFS%20using%20criteria%20in%20rows%20and%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827443%22%20target%3D%22_blank%22%3E%40egspen2%3C%2FA%3E%26nbsp%3BHaven't%20really%20thought%20about%20why%20not%20to%20use%20SUMIF.%20When%20I%20saw%20your%20file%2C%20I%20immediately%20thought%20about%20using%20INDEX%20and%20MATCH%20as%20you%20are%20looking%20to%20return%20an%20amount%20from%20an%20array%20in%20one%20sheet%20based%20on%20row%20and%20column%20headers%20in%20another.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello -

 

In the attached file, I am trying to create a "consolidating" income statement. I am trying to do this via the 'SUMIFS' function as I have two criteria. The criteria are:

 

1) the month on row 2 of 'Income Statement 2' tab agrees to the respective date (row 4) on the Consolidating tab

2) the "Consol PL ref" in column B of 'Income Statement 2' tab agrees to the respective income statement caption in column C of of the 'Consolidating' tab.

 

As there are two criteria, I've tried to use a SUMIFS in cell E7 but can't figure out what the issue is.

7 Replies
Highlighted

@egspen2 Use INDEX/MATCH instead. Demonstrated it in your attached file with the help of some named ranges to make the formula easier to read/maintain.

Highlighted

@Riny_van_Eekelen Thanks. I was curious if Index+Match+Match was going to be the response. Do I correctly infer then that SUMIFS is not able to do what I was trying to do? If so, can you briefly explain why?

Highlighted

@egspen2 Haven't really thought about why not to use SUMIF. When I saw your file, I immediately thought about using INDEX and MATCH as you are looking to return an amount from an array in one sheet based on row and column headers in another.

 

 

Highlighted

@Riny_van_Eekelen Makes sense. While I follow the concept of index/match / match and how you set it up in the example with the named ranges, I can't seem to get it to work when I tried copying over (see attached). My first thought is maybe there's something wrong with the named ranges or inconsistent formatting between tabs? Can't figure out why I'm getting $0 on rows 7 & 9 on the 'Consolidating' tab.

Highlighted

@egspen2 Just changed some references in the named ranges. I also removed the repeated PL references in column B on the GMD Master, as MATCH finds the first one, and you only want the number on the row with the sub-total. Seems to work as desired.

Highlighted

@Riny_van_Eekelen Thanks. I guess my last question would just be to understand what changes you made to the references in the named ranges because I still can't get it to work.

 

Understood on the repeating labels comment that you made - that's why I originally thought of using a SUMIFS.

Highlighted

@egspen2 With regard to the named ranges, make sure that the rows and columns in the different ranges "line-up". Your data is in rows 7 to 34, so the row headers should also be in rows 7 to 34. I believe you had them in rows 5 to 31. Similar for the columns. You can always compare my last file to your last one and see how the named ranges were defined in both.