Forum Discussion
SUMIFS using criteria in rows and columns
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.
