11-09-2020 05:59 AM
11-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.
11-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.
11-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.
11-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.
11-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.
11-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.