Lookup table array and sum all rows that are applicable

Nubie2019

New Member
Joined
Mar 6, 2019
Messages
9
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.
JanFebMar
Apples1611
Oranges61116
Apples111621
Lemons162126

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,774
with PowerQuery unpivot columns
Code:
[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

FruitsJanFebMarFruitsApples
Apples
1​
6​
11​
Oranges
6​
11​
16​
AttributeSum of Value
Apples
11​
16​
21​
Jan
12​
Lemons
16​
21​
26​
Feb
22​
Mar
32​
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this formula

Excel Workbook
ABCDEFGHI
1FRUITJanFebMarFruitMonthSum
2Apples1611ApplesJan=H2)*(Tabla1:]))]12
3Oranges61116
4Apples111621
5Lemons162126
Hoja24
 

Nubie2019

New Member
Joined
Mar 6, 2019
Messages
9
with PowerQuery unpivot columns
Code:
[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

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Fruits[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Jan[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Feb[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Mar[/COLOR]FruitsApples
Apples
1​
6​
11​
Oranges
6​
11​
16​
AttributeSum of Value
Apples
11​
16​
21​
Jan
12​
Lemons
16​
21​
26​
Feb
22​
Mar
32​

<tbody>
</tbody>


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

Nubie2019

New Member
Joined
Mar 6, 2019
Messages
9

ADVERTISEMENT

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

New Member
Joined
Mar 6, 2019
Messages
9
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. I appreciate your kind comments.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,163
Messages
5,527,163
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top