SUMIFS across rows

W

WWII_Buff

Board Regular
Joined
Nov 13, 2017
Messages
88
Hello all!
I am trying to create a SUMIFS statement that will use drop-down box values to select sum totals in a pivot table by Period (P1 - P6) for all categories from A:E.

Excel 2013/2016
ABCDEFGHIJK
Values
Factory
HackettstownHKP Line 1 Non-NutHKP L1 General Expense/AllocProd Exp - ConsumablesExpense
HackettstownHKP Line 1 Non-NutHKP L1 MaintenanceProd Exp - ConsumablesExpense
HackettstownHKP Line 1 Non-NutHKP Milk Chocolate Shift LeadsProd Exp - ConsumablesExpense
HackettstownHKP Line 1 Non-NutHKP Milk ProcessProd Exp - ConsumablesExpense
HackettstownHKP Line 1 Non-NutHKP Packing Room -Milk ChocoProd Exp - ConsumablesExpense
HackettstownHKP Line 1 Non-NutHKP Support Value Stream Team CaptainsProd Exp - ConsumablesExpense
Grand Total

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]Node[/TD]
[TD="align: center"]Cost Center Description[/TD]
[TD="align: center"]Secondary Expense[/TD]
[TD="align: center"]Expense Type[/TD]
[TD="align: center"] P1[/TD]
[TD="align: center"] P2[/TD]
[TD="align: center"] P3[/TD]
[TD="align: center"] P4[/TD]
[TD="align: center"] P5[/TD]
[TD="align: center"] P6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] 13.00[/TD]
[TD="align: right"] 648.00[/TD]
[TD="align: right"] 1,726.00[/TD]
[TD="align: right"] 746.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] 5,889.00[/TD]
[TD="align: right"] 2,864.00[/TD]
[TD="align: right"] 3,116.00[/TD]
[TD="align: right"] 446.00[/TD]
[TD="align: right"] 1,993.00[/TD]
[TD="align: right"] 1,376.00[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] 181.00[/TD]
[TD="align: right"] 176.00[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] 20.00[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] 125.00[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] 14.00[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] 191.00[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 6,070.00[/TD]
[TD="align: right"] 3,040.00[/TD]
[TD="align: right"] 3,129.00[/TD]
[TD="align: right"] 1,128.00[/TD]
[TD="align: right"] 3,844.00[/TD]
[TD="align: right"] 2,313.00[/TD]

</tbody>
2017 Pivot​



The drop-down items are below:
Excel 2013/2016
BC
Period
Factory
Node
Cost Center Description
Secondary Expense
Expense Type
Total

<tbody>
[TD="align: center"]2[/TD]

[TD="align: center"] P5[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Hackettstown[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]HKP Line 1 Non-Nut[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]HKP L1 General Expense/Alloc[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]Prod Exp - Consumables[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]Expense[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]$1,726.00[/TD]

</tbody>
Hackettstown​



The answer I am looking for is the sum total of P5 in this case is $1,726.00

Thank you!
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Something like:

=SUMIFS(INDEX(Sheet1!F:K,,MATCH(C2,Sheet1!F2:K2,0)),Sheet1!A:A,C3,Sheet1!B:B,C4,Sheet1!C:C,C5,Sheet1!D:D,C6,Sheet1!E:E,C7)
 
Upvote 0
Hi, maybe you can adapt this, but if it is always a single value you are extracting then I think you would be better using the GETPIVOTDATA() function.


Excel 2013/2016
ABCDEFGHIJK
2FactoryNodeCost Center DescriptionSecondary ExpenseExpense TypeP1P2P3P4P5P6
3HackettstownHKP Line 1 Non-NutHKP L1 General Expense/AllocProd Exp - ConsumablesExpense-136481726746
4HackettstownHKP Line 1 Non-NutHKP L1 MaintenanceProd Exp - ConsumablesExpense58892864311644619931376
5HackettstownHKP Line 1 Non-NutHKP Milk Chocolate Shift LeadsProd Exp - ConsumablesExpense181176-20--
6HackettstownHKP Line 1 Non-NutHKP Milk ProcessProd Exp - ConsumablesExpense------
7HackettstownHKP Line 1 Non-NutHKP Packing Room -Milk ChocoProd Exp - ConsumablesExpense----125-
8HackettstownHKP Line 1 Non-NutHKP Support Value Stream Team CaptainsProd Exp - ConsumablesExpense---14-191
9Grand Total607030403129112838442313
10
11
12
13
14PeriodP5
15FactoryHackettstown
16NodeHKP Line 1 Non-Nut
17Cost Center DescriptionHKP L1 General Expense/Alloc
18Secondary ExpenseProd Exp - Consumables
19Expense TypeExpense
20
21Total1726
Sheet1
Cell Formulas
RangeFormula
B21=SUMIFS(INDEX(F3:K8,0,MATCH(B14,F2:K2,0)),A3:A8,B15,B3:B8,B16,C3:C8,B17,D3:D8,B18,E3:E8,B19)
 
Last edited:
Upvote 0
steve the fish said:
Something like:

=SUMIFS(INDEX(Sheet1!F:K,,MATCH(C2,Sheet1!F2:K2,0)),Sheet1!A:A,C3,Sheet1!B:B,C4,Sheet1!C:C,C5,Sheet1!D:D,C6,Sheet1!E:E,C7)
Click to expand...

Thank you both for your reply!

Since I will not always have a single value, I used Steve's formula but I get a #REF error.
 
Last edited:
Upvote 0
My suggestion is the same as Steve's (albeit using a different layout) - I didn't see that he had posted before I replied. Are you sure the period in C5 has a match in F2:K2? Did you update the sheet names appropriately?
 
Last edited:
Upvote 0
Sorry FormR, Steve's formula worked! I left out the ",,". I will use your format when I get more comfortable with the request.

Thank you both!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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