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:

	Q1	Q2	Q3	Q4
Apples	90	20	5	90
Oranges	20	20	20	20
Oranges	40	30	40	40
Oranges	50	40	5	50
Apples	80	70	80	80
Mangoes	20	20	20	20
Mangoes	30	10	30	30

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

Sheet 2:

	Total
Apples	(Formula)	should be 515
Oranges	(Formula)
Mangoes	(Formula)

<colgroup><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[sandy666]

maybe it will help with PowerQuery:

Item	Q1	Q2	Q3	Q4		Item	Sum
Apples	90​	20​	5​	90​		Apples	515​
Oranges	20​	20​	20​	20​		Oranges	375​
Oranges	40​	30​	40​	40​		Mangoes	180​
Oranges	50​	40​	5​	50​
Apples	80​	70​	80​	80​
Mangoes	20​	20​	20​	20​
Mangoes	30​	10​	30​	30​

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