# How to sum OFFSET & MATCH

#### JayY1

##### New Member
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.

#### Attachments

• 1615320349377.png
14.9 KB · Views: 1
• 1615320373294.png
15.8 KB · Views: 1
• 1615322315277.png
15.2 KB · Views: 1
• 1615322409257.png
12 KB · Views: 1

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### jasonb75

##### Well-known Member
Perhaps enter this in Budget C2, then drag right and fill down.
Excel Formula:
``=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

##### New Member
I would use SUMIF instead. In Budget!C2 the formula would be
Excel Formula:
``=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

##### Well-known Member
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.

Replies
1
Views
121
Replies
7
Views
156
Replies
0
Views
103
Replies
6
Views
447
Replies
1
Views
107

1,129,932
Messages
5,639,067
Members
417,067
Latest member
rohitbabshet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back