SUMIFS using criteria in rows and columns

Copper 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

@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.

@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?

@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.

 

 

@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.

@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.

@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.

@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.