Sumifs, vlookup, match and 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: 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: 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. 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.

 

[JazzSP8]

This might lead to more questions than answers, but...

Using the example you've given there...

In Sheet 2 A2 use;

=IFERROR(INDEX(Sheet1!$A$2:$A$7,MATCH(0,COUNTIF(Sheet1!F$1:$F1,Sheet1!$A$2:$A$7),0)),"")

It is an array formula so you'll need to confirm with CTRL, Shift and Enter (if done correctly you'll get "{" at the start of the formula and "}" at the end of it).

In Sheet 2 B2 use;

=SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

It's probably not a perfect solution but it might put you on the right path

 

[Astrid123]

Thank you for your answer! I see what you mean.

The point is, at Sheet1 the headers (so let's call it row A) are dynamic.
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..

 

[JazzSP8]

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..

Ahhhhhhhhhh. Right - So you'd need to find the Column with the data in before you can make the list?

That is a bit beyond me I'm afraid to say, I've had a quick play but can't get it to work - I do feel as though it should be doable though, if I think of something I'll come back.

 

[Astrid123]

Anyone with a suggestion?