How to sum OFFSET & MATCH

[JayY1]

Hello,

I have several categories in a budget numbered off like below in column A:

Worksheet "Budget"

On a separate worksheet, I have dozens of subcategories that fall under one of those 5 groups (gas and electricity for Utilities, flight and hotel for Recreation, etc.)

Worksheet "Expenses"

I'm trying to consolidate each of the subcategories with a number that will roll up into the respective categories. For example, I have the annual totals for electricity, gas, and water and want to roll them up into "Utilities" in the Budget worksheet which I would do by putting a "1" in column A and then using an OFFSET and MATCH function in cell C2 of the Budget worksheet to match each instance of "1" and for each specific year. I am using this formula:

=OFFSET('Expenses'!$A$1,MATCH(Budget!$A2,'Expenses'!$A:$A,0)-1,MATCH(Budget!C$2,'Expenses'!$1:$1,0)-1)

The issue is that it only pulls the first instance of "1" it sees, instead of summing every specific instance.

I am also open to a more efficient way of doing it if there is one. I've looked into doing a SUMIF, but that would not take into account each individual year.

 

[jasonb75]

Perhaps enter this in Budget C2, then drag right and fill down.

=SUMIFS(INDEX('Expenses'!$C$4:$E$9,0,MATCH(C$1,'Expenses'!$C$1:$E$1,0)),'Expenses'!$A$4:$A$9,$A2)

Based on the visible portion of the expenses sheet, the ranges will likely need to be increased.

 

[canuck13]

I would use SUMIF instead. In Budget!C2 the formula would be

=SUMIF(Expenses!$A:$A,$A2,Expenses!C:C)

. Assuming your years are in the same order in both worksheets you should be able to copy that formula to all other cells. If years may be in a different order you would need to use match to determine column.

 

[jasonb75]

Assuming your years are in the same order in both worksheets

In theory what you say should work fine, in reality, assuming is always a bad idea, matching the correct column is safer.