Sumifs with a dynamic header

[Astrid123]

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:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Animal[/TD]
[TD]Costs[/TD]
[/TR]
[TR]
[TD]Monkey[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Monkey[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Monkey[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


And sheet 2

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Costs[/TD]
[/TR]
[TR]
[TD]Monkey[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


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!

 

[admiral100]

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]

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]

If you have a table on Sheet 1 (say, Table1), then just use

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