Sumifs Subtotal Advanced Filtered List

[JJLEFROGGE]

I have a table of data which is advanced filtered. I want to create a sumifs subtotal using 2 criteria.

Period = M134
Variable = N139

Period Range: BV200:BV5199
Variable Range = BY200:BY5199
Sum Range = BZ200:BZ5199

Formula: =SUMPRODUCT(SUBTOTAL(9,OFFSET($BZ$200,ROW($BZ$200:$BZ$5199)-ROW($BZ$200),0,1)),--($BV$200:BV$5199=$M$134),--($BY$200:$BY$5199=N139))

Formula is returning #N/A

Note, the range of data has been pulled through with a vlookup. However, still getting same error when values are pasted into range.

Any help hugely appreciated.

thanks

 

[C Moore]

It appears your ranges are different sizes within sumproduct generating the error, array 1 is only one row high based on offset.

 

[JJLEFROGGE]

It appears your ranges are different sizes within sumproduct generating the error, array 1 is only one row high based on offset.

Thank you. Any idea on how I fix it? I'm a little out of my depth on this one.

 

[CA_Punit]

First I suppose what is the use of using Subtotal and offset function. The result which it displays is the same as it would show in the range
I suppose instead of
SUBTOTAL(9,OFFSET($BZ$200,ROW($BZ$200:$BZ$5199)-ROW($BZ$200),0,1))

Select only $BZ$200:$BZ$5199

Sample Post

Book1
	B	C	D	E	F	G	H	I	J	K	L
2
3	Using Subtotal		132600				100		TRUE		TRUE
4							200		TRUE		TRUE
5	Without using Subtotal		132600				300		TRUE		TRUE
6							400		TRUE		TRUE
7							500		TRUE		TRUE
8							600		TRUE		TRUE
9							700		TRUE		TRUE
10							800		TRUE		TRUE
11							900		TRUE		TRUE
12							1000		TRUE		TRUE
13							1100		TRUE		TRUE
14							1200		TRUE		TRUE
15							1300		TRUE		TRUE
16							1400		TRUE		TRUE
17							1500		TRUE		TRUE
18							1600		TRUE		TRUE
19							1700		TRUE		TRUE
20							1800		TRUE		TRUE
21							1900		TRUE		TRUE
22							2000		TRUE		TRUE
23							2100		TRUE		TRUE
24							2200		TRUE		TRUE
25							2300		TRUE		TRUE
26							2400		TRUE		TRUE
27							2500		TRUE		TRUE
28							2600		TRUE		TRUE
29							2700		TRUE		TRUE
30							2800		TRUE		TRUE
31							2900		TRUE		TRUE
32							3000		TRUE		TRUE
33							3100		TRUE		TRUE
34							3200		TRUE		TRUE
35							3300		TRUE		TRUE
36							3400		TRUE		TRUE
37							3500		TRUE		TRUE
38							3600		TRUE		TRUE
39							3700		TRUE		TRUE
40							3800		TRUE		TRUE
41							3900		TRUE		TRUE
42							4000		TRUE		TRUE
43							4100		TRUE		TRUE
44							4200		TRUE		TRUE
45							4300		TRUE		TRUE
46							4400		TRUE		TRUE
47							4500		TRUE		TRUE
48							4600		TRUE		TRUE
49							4700		TRUE		TRUE
50							4800		TRUE		TRUE
51							4900		TRUE		TRUE
52							5000		TRUE		TRUE
53							5100		TRUE		TRUE
54
55
56
Sheet1

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(SUBTOTAL(9,OFFSET(BZ200,ROW(BZ200:BZ250)-ROW(BZ200),0,1)),--($BV$200:BV$250=$M$134),--($BY$200:$BY$250=N139))
H3:H53	H3	=SUBTOTAL(9,OFFSET(BZ200,ROW(BZ200:BZ250)-ROW(BZ200),0,1))
J3:J53	J3	=($BV$200:BV$250=$M$134)
L3:L53	L3	=($BY$200:$BY$250=N139)
D5	D5	=SUMPRODUCT(H3#,--($BV$200:BV$250=$M$134),--($BY$200:$BY$250=N139))
Dynamic array formulas.

 

[JJLEFROGGE]

First I suppose what is the use of using Subtotal and offset function. The result which it displays is the same as it would show in the range
I suppose instead of
SUBTOTAL(9,OFFSET($BZ$200,ROW($BZ$200:$BZ$5199)-ROW($BZ$200),0,1))

Select only $BZ$200:$BZ$5199

Sample Post

Book1
	B	C	D	E	F	G	H	I	J	K	L
2
3	Using Subtotal		132600				100		TRUE		TRUE
4							200		TRUE		TRUE
5	Without using Subtotal		132600				300		TRUE		TRUE
6							400		TRUE		TRUE
7							500		TRUE		TRUE
8							600		TRUE		TRUE
9							700		TRUE		TRUE
10							800		TRUE		TRUE
11							900		TRUE		TRUE
12							1000		TRUE		TRUE
13							1100		TRUE		TRUE
14							1200		TRUE		TRUE
15							1300		TRUE		TRUE
16							1400		TRUE		TRUE
17							1500		TRUE		TRUE
18							1600		TRUE		TRUE
19							1700		TRUE		TRUE
20							1800		TRUE		TRUE
21							1900		TRUE		TRUE
22							2000		TRUE		TRUE
23							2100		TRUE		TRUE
24							2200		TRUE		TRUE
25							2300		TRUE		TRUE
26							2400		TRUE		TRUE
27							2500		TRUE		TRUE
28							2600		TRUE		TRUE
29							2700		TRUE		TRUE
30							2800		TRUE		TRUE
31							2900		TRUE		TRUE
32							3000		TRUE		TRUE
33							3100		TRUE		TRUE
34							3200		TRUE		TRUE
35							3300		TRUE		TRUE
36							3400		TRUE		TRUE
37							3500		TRUE		TRUE
38							3600		TRUE		TRUE
39							3700		TRUE		TRUE
40							3800		TRUE		TRUE
41							3900		TRUE		TRUE
42							4000		TRUE		TRUE
43							4100		TRUE		TRUE
44							4200		TRUE		TRUE
45							4300		TRUE		TRUE
46							4400		TRUE		TRUE
47							4500		TRUE		TRUE
48							4600		TRUE		TRUE
49							4700		TRUE		TRUE
50							4800		TRUE		TRUE
51							4900		TRUE		TRUE
52							5000		TRUE		TRUE
53							5100		TRUE		TRUE
54
55
56
Sheet1

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(SUBTOTAL(9,OFFSET(BZ200,ROW(BZ200:BZ250)-ROW(BZ200),0,1)),--($BV$200:BV$250=$M$134),--($BY$200:$BY$250=N139))
H3:H53	H3	=SUBTOTAL(9,OFFSET(BZ200,ROW(BZ200:BZ250)-ROW(BZ200),0,1))
J3:J53	J3	=($BV$200:BV$250=$M$134)
L3:L53	L3	=($BY$200:$BY$250=N139)
D5	D5	=SUMPRODUCT(H3#,--($BV$200:BV$250=$M$134),--($BY$200:$BY$250=N139))
Dynamic array formulas.

Thank you. I'm always stunned at how generous contributors are in these forums! Thank you so much - it worked and now I can relax!

 

[C Moore]

I'd recommend named ranges with the SUMIFS function. How is BZ200 returned with vlookup? Does it return text or row number?
Define name dialog:
Name: MySumRange
Refers to: =INDIRECT(vlookup1&":"&vlookup2)

Name: MyCriteriaRange1
Refers to: =INDIRECT(vlookup1&":"&vlookup2)

Name: MyCriteriaRange2
Refers to: =INDIRECT(vlookup1&":"&vlookup2)

Formula for answer =SUMIFS(MySumRange,MyCriteriaRange1,M134,MyCriteriaRange2,N139)

 

[JJLEFROGGE]

I'd recommend named ranges with the SUMIFS function. How is BZ200 returned with vlookup? Does it return text or row number?
Define name dialog:
Name: MySumRange
Refers to: =INDIRECT(vlookup1&":"&vlookup2)

Name: MyCriteriaRange1
Refers to: =INDIRECT(vlookup1&":"&vlookup2)

Name: MyCriteriaRange2
Refers to: =INDIRECT(vlookup1&":"&vlookup2)

Formula for answer =SUMIFS(MySumRange,MyCriteriaRange1,M134,MyCriteriaRange2,N139)

Thank you! All fixed