Excel Formula Help - need excel to look up a text and sum all values that have that text in multiple rows and columns

[mnminutoli]

I am hoping someone could help me with an excel function I am struggling with.

I have two tables one in sheet one and one in sheet two (as shown below). I want to generate an excel formula that will sum all the values for Q1 - Q4 that match each fruit name in the summary table on sheet 2. So for example, apples would be 515. There are multiple rows with the word "apples" and multiple columns (Q1-Q4) that have data for "apples". Does anyone know how I can do this?

Sheet 1:

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"] Q1[/TD]
[TD="width: 64"] Q2[/TD]
[TD="width: 64"] Q3[/TD]
[TD="width: 64"] Q4[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Mangoes[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Mangoes[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:

[TABLE="width: 311"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] Total [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples [/TD]
[TD] (Formula)[/TD]
[TD]should be 515[/TD]
[/TR]
[TR]
[TD]Oranges [/TD]
[TD] (Formula)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mangoes[/TD]
[TD] (Formula)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 311"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

maybe it will help with PowerQuery:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Item[/td][td=bgcolor:#5B9BD5]Q1[/td][td=bgcolor:#5B9BD5]Q2[/td][td=bgcolor:#5B9BD5]Q3[/td][td=bgcolor:#5B9BD5]Q4[/td][td][/td][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Sum[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Apples[/td][td=bgcolor:#DDEBF7]

90​

[/td][td=bgcolor:#DDEBF7]

20​

[/td][td=bgcolor:#DDEBF7]

5​

[/td][td=bgcolor:#DDEBF7]

90​

[/td][td][/td][td=bgcolor:#E2EFDA]Apples[/td][td=bgcolor:#E2EFDA]

515​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Oranges[/td][td]

20​

[/td][td]

20​

[/td][td]

20​

[/td][td]

20​

[/td][td][/td][td]Oranges[/td][td]

375​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Oranges[/td][td=bgcolor:#DDEBF7]

40​

[/td][td=bgcolor:#DDEBF7]

30​

[/td][td=bgcolor:#DDEBF7]

40​

[/td][td=bgcolor:#DDEBF7]

40​

[/td][td][/td][td=bgcolor:#E2EFDA]Mangoes[/td][td=bgcolor:#E2EFDA]

180​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Oranges[/td][td]

50​

[/td][td]

40​

[/td][td]

5​

[/td][td]

50​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Apples[/td][td=bgcolor:#DDEBF7]

80​

[/td][td=bgcolor:#DDEBF7]

70​

[/td][td=bgcolor:#DDEBF7]

80​

[/td][td=bgcolor:#DDEBF7]

80​

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

[tr=bgcolor:#FFFFFF][td]Mangoes[/td][td]

20​

[/td][td]

20​

[/td][td]

20​

[/td][td]

20​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mangoes[/td][td=bgcolor:#DDEBF7]

30​

[/td][td=bgcolor:#DDEBF7]

10​

[/td][td=bgcolor:#DDEBF7]

30​

[/td][td=bgcolor:#DDEBF7]

30​

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

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Item"}, {{"Sum", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"[/SIZE]

 

[Marcelo Branco]

Try

Sheet2
Formula in B2 copied down
=SUMPRODUCT((Sheet1!A$2:A$8=A2)*Sheet1!B$2:E$8)

M.