Nov 09 2020 05:59 AM
Nov 09 2020 05:59 AM
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.
Nov 09 2020 07:02 AM
@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.
Nov 09 2020 07:13 AM
@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.
Nov 09 2020 07:29 AM
@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.
Nov 09 2020 08:10 AM
@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.
Nov 09 2020 08:20 AM
@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.