Formula SUBTOTAL within SUMPRODUCT

[Guinaba]

Hi guys,

I am using the below formula to sum up all the rows on col. K based on the condition "Green", however if I try to add another condition say...--($M$62:$M$1056="Square") keeping both in the formula, the formula doesn't work. Any suggestion?

Ok =SUMPRODUCT(SUBTOTAL(109,OFFSET(K$62,ROW(K$62:K$1056)-ROW(K$62),,1)),--($J$62:$J$1056="Green"))

Not ok =SUMPRODUCT(SUBTOTAL(109,OFFSET(K$62,ROW(K$62:K$1056)-ROW(K$62),,1)),--($J$62:$J$1056="Green"),--($M$62:$M$1056="Square"))

Cheers

 

[Georgiboy]

It is working my end:

Maybe you could show us some data with it not working using the XL2BB addin?

 

[Fluff]

Works fine for me as well

Fluff.xlsm
	B								J	K	L	M
62	5								green	1		square
63	5									2
64										3
65									green	4		square
66										5
67										6
Data

Cell Formulas
Range	Formula
B62	B62	=SUMPRODUCT(SUBTOTAL(109,OFFSET(K$62,ROW(K$62:K$1056)-ROW(K$62),,1)),--($J$62:$J$1056="Green"))
B63	B63	=SUMPRODUCT(SUBTOTAL(109,OFFSET(K$62,ROW(K$62:K$1056)-ROW(K$62),,1)),--($J$62:$J$1056="Green"),--($M$62:$M$1056="Square"))

Fluff.xlsm
	A	B								J	K	L	M
62		1								green	1		square
63		1									2
64											3
66											5
67											6
Data

Cell Formulas
Range	Formula
B62	B62	=SUMPRODUCT(SUBTOTAL(109,OFFSET(K$62,ROW(K$62:K$1056)-ROW(K$62),,1)),--($J$62:$J$1056="Green"))
B63	B63	=SUMPRODUCT(SUBTOTAL(109,OFFSET(K$62,ROW(K$62:K$1056)-ROW(K$62),,1)),--($J$62:$J$1056="Green"),--($M$62:$M$1056="Square"))

 

[Guinaba]

Thanks a lot @Fluff and @Georgiboy! Indeed the formula is working, silly mistake from my side.
Cheers

 

[Fluff]

Glad it's sorted & thanks for letting us know.

 

[Georgiboy]

Not sure what happened to my post (XL2BB part is missing under the ':') but glad you got it working.