Using Text as a dependent variable in excel

[Ryprice]

I've been trying to figure out a way to create a function that will not only consolidate data from any duplicate entries but also be dependant on the header of the row. I know that if I place a =sumif function into this table, I can get it to consolidate the duplicates but I'm having difficulty finding a way to also have it rely on the header. Is there an easier function to use or is there a function that I can place within the sumif to have it look at both fields? I'll attach screenshots to help explain what I mean.

Capt 1.JPG

so for example, this is the raw data and the highlighted cells are my headers. I want to make a function in a new table that will look at both the rows (A,B,C,D,A,H,E,D,C) and compine the duplicates but also look at the top (Samp 1, Samp 2, Samp 3) and adjust acordingly. Like if I were to replace Samp 3 with Samp 1, I would want the function to see that there are now two "Samp 1" and combine them.

Thank you in advance!

[xlnitwit]

Hi,

I think SUMPRODUCT is what you want but can you post a sample workbook showing what you have and what result you want?

[Ryprice]

Capture.JPG

So I want to have the function consolidate the data on the left side into the table on the right. But while doing so, I want it to check the column name and verify that it goes into the correct column. So for example if Column B moved to Column C, I want to have a function that'll see that and still make sure to place it into column H. I want to try and make it as automated as possible so someone could just dump data into it without having to format it.

[63falcondude]

In H2, try

=SUMPRODUCT(($B$2:$D$10)*($A$2:$A$10=$G2)*($B$1:$D$1=H$1))

[Ryprice]

That worked flawlessly. Thank you so much!

[63falcondude]

You're welcome. Glad to help!