Sumifs with multiple columns - Possible Vlookup needed

[danvorn]

I have data that I need to sum together and then use a vlookup to assign to an individual. I have been trying to use SUMIFS to use a double criteria, but it hasn't been working. Any ideas?

I want to be able to use a formula to pull and summarize the data below as follows

[TABLE="width: 299"]
<tbody>[TR]
[TD][/TD]
[TD] Cost A
[/TD]
[TD] Cost B
[/TD]
[TD] Cost C
[/TD]
[/TR]
[TR]
[TD]Jeff
[/TD]
[TD] 150
[/TD]
[TD] 100
[/TD]
[TD] 150
[/TD]
[/TR]
[TR]
[TD]Jay
[/TD]
[TD] 150
[/TD]
[TD] 100
[/TD]
[TD] 150
[/TD]
[/TR]
[TR]
[TD]Kaylie
[/TD]
[TD] 150
[/TD]
[TD] 100
[/TD]
[TD] 150
[/TD]
[/TR]
</tbody>[/TABLE]


Here is an example of the data.

[TABLE="width: 395"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Jeff
[/TD]
[TD]Jay
[/TD]
[TD]Kaylie
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] A
[/TD]
[TD] Cost A
[/TD]
[TD] 50
[/TD]
[TD] 50
[/TD]
[TD] 50
[/TD]
[/TR]
[TR]
[TD] A
[/TD]
[TD] Cost A
[/TD]
[TD] 100
[/TD]
[TD] 100
[/TD]
[TD] 100
[/TD]
[/TR]
[TR]
[TD] C
[/TD]
[TD] Cost C
[/TD]
[TD] 50
[/TD]
[TD] 50
[/TD]
[TD] 50
[/TD]
[/TR]
[TR]
[TD] C
[/TD]
[TD] Cost C
[/TD]
[TD] 100
[/TD]
[TD] 100
[/TD]
[TD] 100
[/TD]
[/TR]
[TR]
[TD] B
[/TD]
[TD]Cost B
[/TD]
[TD] 25
[/TD]
[TD] 25
[/TD]
[TD] 25
[/TD]
[/TR]
[TR]
[TD] B
[/TD]
[TD]Cost B
[/TD]
[TD] 75
[/TD]
[TD] 75
[/TD]
[TD] 75
[/TD]
[/TR]
</tbody>[/TABLE]

 

[bbott]

Welcome to the board.

Have you tried using a pivot table?

 

[danvorn]

No, I need to export the data into a different table and can't manipulate the original data.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td]Jeff[/td][td]Jay[/td][td]Kaylie[/td][td][/td][td][/td][td]Cost A[/td][td]Cost B[/td][td]Cost C[/td][/tr]

[tr][td]

2​

[/td][td]A[/td][td]Cost A[/td][td]

50​

[/td][td]

50​

[/td][td]

50​

[/td][td][/td][td]Jeff[/td][td]

150​

[/td][td]

100​

[/td][td]

150​

[/td][/tr]

[tr][td]

3​

[/td][td]A[/td][td]Cost A[/td][td]

100​

[/td][td]

100​

[/td][td]

100​

[/td][td][/td][td]Jay[/td][td]

150​

[/td][td]

100​

[/td][td]

150​

[/td][/tr]

[tr][td]

4​

[/td][td]C[/td][td]Cost C[/td][td]

50​

[/td][td]

50​

[/td][td]

50​

[/td][td][/td][td]Kaylie[/td][td]

150​

[/td][td]

100​

[/td][td]

150​

[/td][/tr]

[tr][td]

5​

[/td][td]C[/td][td]Cost C[/td][td]

100​

[/td][td]

100​

[/td][td]

100​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]B[/td][td]Cost B[/td][td]

25​

[/td][td]

25​

[/td][td]

25​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]B[/td][td]Cost B[/td][td]

75​

[/td][td]

75​

[/td][td]

75​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

H2, copied across and down:

=SUMIFS(INDEX($C:$E,0,MATCH($G2,INDEX($C:$E,1,0),0)),$B:$B,H$1)

 

[GerryZ]

try this formula!

Book1
	A	B	C	D
1		Jeff	Jay	Kaylie
2	Cost A	50	50	50
3	Cost A	100	100	100
4	Cost C	50	50	50
5	Cost C	100	100	100
6	Cost B	25	25	25
7	Cost B	75	75	1
8
9
10		Cost A	Cost B	Cost C
11	Jeff	150	100	150
12	Jay	150	100	150
13	Kaylie	150	26	150
Foglio2

Cell Formulas
Range	Formula
B11	=SUMPRODUCT($B$2:$D$7*($A$2:$A$7=B$10)*($B$1:$D$1=$A11))

 

[danvorn]

Wow! Thank you! I have no idea how that works...but it did!

 

[Aladin Akyurek]

Wow! Thank you! I have no idea how that works...but it did!

You are welcome.