Lookup table array and sum all rows that are applicable

[Nubie2019]

Hello,

I want to sum all "Apples" under the "Jan" Column in the following table. I want to make the formula dynamic by having the formula lookup the entire table and select the correct columns and rows rather than using "Sumifs" on a per column basis. Any help will be appreciated.

The formula should add the items in RED.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]11[/TD]
[TD]16[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[TD]16[/TD]
[TD]21[/TD]
[TD]26[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

with PowerQuery unpivot columns

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Fruits"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then use PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Fruits[/td][td=bgcolor:#5B9BD5]Jan[/td][td=bgcolor:#5B9BD5]Feb[/td][td=bgcolor:#5B9BD5]Mar[/td][td][/td][td=bgcolor:#DDEBF7]Fruits[/td][td=bgcolor:#DDEBF7]Apples[/td][/tr]

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

1​

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

6​

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

11​

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

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

6​

[/td][td]

11​

[/td][td]

16​

[/td][td][/td][td=bgcolor:#DDEBF7]Attribute[/td][td=bgcolor:#DDEBF7]Sum of Value[/td][/tr]

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

11​

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

16​

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

21​

[/td][td][/td][td]Jan[/td][td]

12​

[/td][/tr]

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

16​

[/td][td]

21​

[/td][td]

26​

[/td][td][/td][td]Feb[/td][td]

22​

[/td][/tr]

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

32​

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

 

[DanteAmor]

Try this formula

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	FRUIT	Jan	Feb	Mar			Fruit	Month	Sum
2	Apples	1	6	11			Apples	Jan	=H2)*(Tabla1:]))]12
3	Oranges	6	11	16
4	Apples	11	16	21
5	Lemons	16	21	26
Hoja24

 

[Nubie2019]

with PowerQuery unpivot columns

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Fruits"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then use PivotTable

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Fruits[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Jan[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Feb[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Mar[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]Fruits[/TD]
[TD="bgcolor: #DDEBF7"]Apples[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Apples[/TD]
[TD="bgcolor: #DDEBF7"]

1​

[/TD]
[TD="bgcolor: #DDEBF7"]

6​

[/TD]
[TD="bgcolor: #DDEBF7"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Oranges[/TD]
[TD]

6​

[/TD]
[TD]

11​

[/TD]
[TD]

16​

[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]Attribute[/TD]
[TD="bgcolor: #DDEBF7"]Sum of Value[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Apples[/TD]
[TD="bgcolor: #DDEBF7"]

11​

[/TD]
[TD="bgcolor: #DDEBF7"]

16​

[/TD]
[TD="bgcolor: #DDEBF7"]

21​

[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]

12​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Lemons[/TD]
[TD]

16​

[/TD]
[TD]

21​

[/TD]
[TD]

26​

[/TD]
[TD][/TD]
[TD]Feb[/TD]
[TD]

22​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar[/TD]
[TD]

32​

[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Was hoping for a formula solution. But will keep this in mind...thanks again!!

 

[Nubie2019]

Thanks! I tried this and get a
#VALUE ! Error. The formula I have is "=SUMPRODUCT(Table1[Fruit]=G2)*(Table1[[#Headers],[Jan]:[Mar]]=H2*(Table1[[Jan]:[Mar]])". Does it work for you?

 

[Nubie2019]

Thanks Dante! I tried this and get a
#VALUE ! Error. The formula I have is "=SUMPRODUCT(Table1[Fruit]=G2)*(Table1[[#Headers],[Jan]:[Mar]]=H2*(Table1[[Jan]:[Mar]])". Does it work for you?

 

[DanteAmor]

Try with my file:

https://www.dropbox.com/s/b27lb8vyaz7w6q7/fruits.xlsx?dl=0

 

[Nubie2019]

Perfect! Works like a charm. Thanks so much Dante!

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.