Adding together multiple rows values from columns in unorganized fashion

[Gadman]

I have a dump file that unfortunately doesn't organize the rows or columns of data in the same order every time, but does match the right values as needed. I have been working on a formula that can add up multiple row values across several columns and just run into a wall every time

Simplified:
For example in Cells B1:D1 I have Jan, Sue, Billy
And in cells A2:A4 I have Cars, Dollars, Pets

Again, the Cars, dollars, and pets can organize sometimes as dollars, pets, cars... Same with Jan Sue and Billy, they could be in any particular order... or any other combination from a dump file that's automatically generated.

I need a formula that can, for example, add up how many Dollars and Cars that Jan has, or how many Pets and Dollars Sue has, or How many Dollars and Pets Billy has... I'm sure once I see a working formula, I can grasp the basis and edit it as I need specifically.

Any help greatly appreciated, thanks.

	Jan	Sue	Billy
Cars	3	2	5
Dollars	700	1000	800
Pets	2	0	10

 

[gaz_chops]

Try sumproduct -

=SUMPRODUCT(($B$1:$E$1=$A11)*($A$2:$A$4=$B11)*$B$2:$E$4)

 

[Gadman]

Try sumproduct -

=SUMPRODUCT(($B$1:$E$1=$A11)*($A$2:$A$4=$B11)*$B$2:$E$4)

View attachment 12795

Originally I didn't think that would work because I needed to add two different values, but I simply copied the formula and added another value beside cars and added cars and whatever.

That worked great, thanks!

SOLVED

 

[gaz_chops]

You're welcome, glad it worked.