How to sum OFFSET & MATCH

JayY1

New Member
Joined
Mar 9, 2021
Messages
1
Office Version
  1. 2019
Hello,

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

Worksheet "Budget"
1615322422063.png


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"
1615322396322.png


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
    1615320349377.png
    14.9 KB · Views: 1
  • 1615320373294.png
    1615320373294.png
    15.8 KB · Views: 1
  • 1615322315277.png
    1615322315277.png
    15.2 KB · Views: 1
  • 1615322409257.png
    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
Joined
Dec 30, 2008
Messages
12,644
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 8, 2014
Messages
31
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
Joined
Dec 30, 2008
Messages
12,644
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top