Sumifs with a dynamic header

Astrid123

New Member
Joined
Sep 25, 2017
Messages
16
Hello everybody

The following situation I'm struggling for some time right now, I feel like I'm close to solving it, though I do not succeed.

What I want:

Let's say that in sheet 1 I have one table containing data, with a header, names and amounts.
In sheet 2 I want to lookup the sum of amounts based on names and the dynamic header.

For example:

Table sheet 1:

AnimalCosts
Monkey8
Monkey5
Rabbit2
Dog1
Dog3
Monkey4

<tbody>
</tbody>


And sheet 2

Costs
Monkey17
Rabbit2
Dog4

<tbody>
</tbody>


I want the formula for B2, B3 and B4, but since the headers in sheet 1 are dynamically I don't want a fixed formula. Sheet 2, row A is not dynamic, the headers Animal and costs in this example I can just write it down. But for Sheet 1 the name Animal can be placed in column A but in column B or C or whatever as well. For this reason I want to apply the index match function. But I don't know exactly how to do this, I guess it needs to be something with Sumifs, Vlookup and match. But Im not sure how to do it exactly.


Hope you can help me out!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
Hi,

Try this :

Crtl+Shift+Enter NOT just Enter and change "Animal"=Sheet1!$A$1:$B$1 as needed...


=SUM((IF("Animal"=Sheet1!$A$1:$B$1,IF(Shet2!A2=Sheet1!$A$2:$A$7,Sheet1!$B$2:$B$7))))
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,656
Office Version
  1. 2010
Platform
  1. Windows
This might get you started it, it uses sum instead of sumif but the technique is the same;

=SUM(OFFSET(A2,0,MATCH("Animal",A1:T1,0),7,1))

Change the last colum T1 as required, and the "height" 7 must be change as required too.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,631
If you have a table on Sheet 1 (say, Table1), then just use

=SUMIF(Table1[Animal],A2,Table1[Costs])
 

Watch MrExcel Video

Forum statistics

Threads
1,122,653
Messages
5,597,377
Members
414,141
Latest member
Joey_T92

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