Nested Index Match with multiple criteria - possible?

[gwstudent]

Hi -

I have posted a sample file here: https://docs.google.com/file/d/0B2V4Nkca2ASFby1vcDMtNWN4UjA/edit?usp=sharing


Suppose I have a budget with a list of tasks that will be worked on. Each month, costs related to this list of tasks come in. Is there a way to roll-up the costs associated with a specific task and matching criteria? There will be a total of 13 worksheets - 1 for the budget (MasterBudget) and one worksheet for each month.


The logic is essentially: Find all costs, from Jan - Dec, that match the task (Business Purpose) and that match the category (there are only 2) in the MasterBudget. I have created named ranges for the months (using Offset function which I realize is volatile but I don't know how better to do this) and the first column in the MasterBudget.


I have no problem finding a single cost using the following but I can't find a way to get all the costs.

=IFERROR(IF(MATCH([@[MasBudget]], Jan,0),INDEX(Jan,2,1)),"")


Any direction or pointers would be appreciated.

 

[Jai9]

Hi,


Your first 3 sheets in Sheet1, Sheet2, Sheet3 and result in Sheet4.

With the data starting in cell A1 of all sheets,


=SUMIFS(Sheet1!$B$2:$B$8,Sheet1!$E$2:$E$8,Sheet4!A2,Sheet1!$F$2:$F$8,1)

+SUMIFS(Sheet2!$B$2:$B$4,Sheet2!$E$2:$E$4,Sheet4!A2,Sheet2!$F$2:$F$4,1)

+SUMIFS(Sheet3!$B$2:$B$5,Sheet3!$E$2:$E$5,Sheet4!A2,Sheet3!$F$2:$F$5,1)


This is for category 1. For category 2, change 1 at the end of each sumifs to 2.


Jai

 

[gwstudent]

Jai -


Thanks! That was way more easy than I thought it would be. Awesome.