Sumifs on Pivot Table Plus

[wsnyder]

Hi,

I am trying to create a SUMIFS() to Sum only certain columns in my Pivot Table
I keep getting a #VALUE! error no matter what I do
Added the Range of numbers manually in Row 2
Any ideas?
Thanks,
w

Ex_Sumifs.xlsx
	A	B	C	D	E	F	G	H
1	Dates	(All)
2		1	2	3	4	5	6	7
3	Sum of Sales	Column Labels
4	Row Labels	Acme	BronxBusters	Dooleywhops	Thingamajigs	Whatchamacallits	WidgetsRUS	Grand Total
5	East	$ 88,975.43	$ 88,435.50	$ 84,353.30	$ 95,615.76	$ 78,275.46	$ 96,829.62	$ 532,485.08
6	North	$ 82,812.59	$ 84,133.30	$ 96,316.45	$ 88,152.49	$ 93,531.99	$ 92,252.10	$ 537,198.91
7	South	$ 95,665.94	$ 86,223.02	$ 98,519.41	$ 90,297.41	$ 87,969.34	$ 84,538.30	$ 543,213.43
8	West	$ 88,643.94	$ 93,228.47	$ 82,503.86	$ 83,993.66	$ 79,119.62	$ 82,713.71	$ 510,203.26
9	Grand Total	$ 356,097.91	$ 352,020.29	$ 361,693.01	$ 358,059.32	$ 338,896.41	$ 356,333.74	$ 2,123,100.68
10
11			4
12		East	#VALUE!
Sheet2

Cell Formulas
Range	Formula
C2:H2	C2	=+B2+1
C12	C12	=SUMIFS($A$5:$H$9,$B$2:$H$2,"<="&C11,$A$5:$A$9,$B$12)

 

[Alex Blakenburg]

Give this a try:

=SUM(FILTER(FILTER($B$5:$H$8,$B$2:$H$2<=$C$11),$A$5:$A$8=$B$12))

or this:

=SUM(($B$2:$H$2<=$C$11)*($A$5:$A$8=$B$12)*($B$5:$H$8))

if you need it to run on pre 365 Excel change the 2nd one from Sum to SumProduct

 

[wsnyder]

Thanks Alex,

The first formula works great!

Thanks,
w

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[wsnyder]

Hi @Alex Blakenburg

I need to add some additional criteria to the SUM(FILTER(FILTER( Formula
I added but I'm getting a #VALUE! error

Can I not keep adding more nested FILTER statements to get down to the 1 column I need to summarize?
This works as expected down to BDQ$4, but then breaks at BDQ$6

=FILTER(FILTER(FILTER(Pivot_CurrentMonth!$BDE$9:$BDL$1500,
                                         (Pivot_CurrentMonth!$BDC$9:$BDC$1500>=BDQ$2) *
                                         (Pivot_CurrentMonth!$BDC$9:$BDC$1500<=BDQ$3)),
                                         (Pivot_CurrentMonth!$BDE$6:$BDL$6=BDQ$4)),
                                         (Pivot_CurrentMonth!$BDE$8:$BDL$8=BDQ$6))

Thanks,
w

 

[wsnyder]

@Alex Blakenburg

I got it.
I ended up splitting Row from Column criteria and it worked!

Thanks,
-w

=SUM(FILTER(FILTER(Pivot_CurrentMonth!$BDE$9:$BDL$1500,
                                         (Pivot_CurrentMonth!$BDC$9:$BDC$1500>=BDQ$2) *
                                         (Pivot_CurrentMonth!$BDC$9:$BDC$1500<=BDQ$3)),
                                         (Pivot_CurrentMonth!$BDE$6:$BDL$6=BDQ$4) *
                                         (Pivot_CurrentMonth!$BDE$7:$BDL$7=BDQ$5) *
                                         (Pivot_CurrentMonth!$BDE$8:$BDL$8=BDQ$6)))

 

[Alex Blakenburg]

Perfect. Appreciate you letting me know.