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>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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​
 
Upvote 0
Try this formula

Excel Workbook
ABCDEFGHI
1FRUITJanFebMarFruitMonthSum
2Apples1611ApplesJan=H2)*(Tabla1:]))]12
3Oranges61116
4Apples111621
5Lemons162126
Hoja24
 
Upvote 0
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!!
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
I'm glad to help you. I appreciate your kind comments.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top