SUMIFS across rows

[WWII_Buff]

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

	A	B	C	D	E	F	G	H	I	J	K
1						Values
2	Factory	Node	Cost Center Description	Secondary Expense	Expense Type	P1	P2	P3	P4	P5	P6
3	Hackettstown	HKP Line 1 Non-Nut	HKP L1 General Expense/Alloc	Prod Exp - Consumables	Expense	-	-	13.00	648.00	1,726.00	746.00
4	Hackettstown	HKP Line 1 Non-Nut	HKP L1 Maintenance	Prod Exp - Consumables	Expense	5,889.00	2,864.00	3,116.00	446.00	1,993.00	1,376.00
5	Hackettstown	HKP Line 1 Non-Nut	HKP Milk Chocolate Shift Leads	Prod Exp - Consumables	Expense	181.00	176.00	-	20.00	-	-
6	Hackettstown	HKP Line 1 Non-Nut	HKP Milk Process	Prod Exp - Consumables	Expense	-	-	-	-	-	-
7	Hackettstown	HKP Line 1 Non-Nut	HKP Packing Room -Milk Choco	Prod Exp - Consumables	Expense	-	-	-	-	125.00	-
8	Hackettstown	HKP Line 1 Non-Nut	HKP Support Value Stream Team Captains	Prod Exp - Consumables	Expense	-	-	-	14.00	-	191.00
9	Grand Total					6,070.00	3,040.00	3,129.00	1,128.00	3,844.00	2,313.00

<tbody>
</tbody>

2017 Pivot​

The drop-down items are below:
Excel 2013/2016

	B	C
2	Period	P5
3	Factory	Hackettstown
4	Node	HKP Line 1 Non-Nut
5	Cost Center Description	HKP L1 General Expense/Alloc
6	Secondary Expense	Prod Exp - Consumables
7	Expense Type	Expense
8
9	Total	$1,726.00

<tbody>
</tbody>

Hackettstown​

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

Thank you!

 

[steve the fish]

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)

 

[FormR]

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
	A	B	C	D	E	F	G	H	I	J	K
2	Factory	Node	Cost Center Description	Secondary Expense	Expense Type	P1	P2	P3	P4	P5	P6
3	Hackettstown	HKP Line 1 Non-Nut	HKP L1 General Expense/Alloc	Prod Exp - Consumables	Expense		-	13	648	1726	746
4	Hackettstown	HKP Line 1 Non-Nut	HKP L1 Maintenance	Prod Exp - Consumables	Expense	5889	2864	3116	446	1993	1376
5	Hackettstown	HKP Line 1 Non-Nut	HKP Milk Chocolate Shift Leads	Prod Exp - Consumables	Expense	181	176	-	20	-	-
6	Hackettstown	HKP Line 1 Non-Nut	HKP Milk Process	Prod Exp - Consumables	Expense	-	-	-	-	-	-
7	Hackettstown	HKP Line 1 Non-Nut	HKP Packing Room -Milk Choco	Prod Exp - Consumables	Expense	-	-	-	-	125	-
8	Hackettstown	HKP Line 1 Non-Nut	HKP Support Value Stream Team Captains	Prod Exp - Consumables	Expense	-	-	-	14	-	191
9	Grand Total					6070	3040	3129	1128	3844	2313
10
11
12
13
14	Period	P5
15	Factory	Hackettstown
16	Node	HKP Line 1 Non-Nut
17	Cost Center Description	HKP L1 General Expense/Alloc
18	Secondary Expense	Prod Exp - Consumables
19	Expense Type	Expense
20
21	Total	1726
Sheet1

Cell Formulas
Range	Formula
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)

 

[WWII_Buff]

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)

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.

 

[FormR]

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?

 

[WWII_Buff]

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!