Filter excluded rows in array formula

[mrblister]

I have an array formula. This is a simplified example of my formula:

{SUM((A1:A10<>"")*(A1:C10=$I4))}
alternate example: {SUM((A1:A10=$B$1)*(LEN($C$1)=1)*1)+SUM((A1:A10=$B$1)*(LEN($C$1)=2)*0.6)}

I would like to modify the formula, so that if "A1:A10" has a filter applied (from Sort & Filter menu) and certain rows are filtered out, the formula only sums-up the visible rows.
I tried using SUBTOTAL:

{SUBTOTAL(9,(A1:A10<>"")*(A1:C10=$I4))}

But I get an "argument isn't a range" error.

Can anyone assist? Note the formula will be used on a system that doesn't have access to AGGREGATE function.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1	x	x	x		11	11
2
3	x		x
4
5	x		x
6	x	x
7	x
8		x	x
9
10	x
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT((A1:A10<>"")*(A1:C10=$I4))
F1	F1	=SUMPRODUCT(SUBTOTAL(103,OFFSET(A1,ROW(INDIRECT("1:10"))-1,0,1,1))*(A1:C10=$I4))

And with a filter:

Book1
	A	B	C	D	E	F
1	x	x	x		11	5
2
7	x
8		x	x
9
10	x
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT((A1:A10<>"")*(A1:C10=$I4))
F1	F1	=SUMPRODUCT(SUBTOTAL(103,OFFSET(A1,ROW(INDIRECT("1:10"))-1,0,1,1))*(A1:C10=$I4))

The SUBTOTAL(3,OFFSET(A1,ROW(INDIRECT("1:10"))-1,0,1,1)) structure is the key, where it returns a 0 or a 1 for each cell in A1:A10.

 

[mrblister]

Thank you! @Eric W ! Much appreciated!

But I was wondering, do solutions exist that don't use volatile functions?

 

[Eric W]

Not that I'm aware of, even in the newest versions of Excel. You could use a helper column though:

Book1
	A	B	C	D	E	F
1	x	x	x	1	11	11
2				0
3	x		x	1
4				0
5	x		x	1
6	x	x		1
7	x			1
8		x	x	0
9				0
10	x			1
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT((A1:A10<>"")*(A1:C10=$I4))
F1	F1	=SUMPRODUCT(D1:D10*(A1:C10=$I4))
D1:D10	D1	=SUBTOTAL(103,A1)

Book1
	A	B	C	D	E	F
1	x	x	x	1	11	5
2				0
7	x			1
8		x	x	0
9				0
10	x			1
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT((A1:A10<>"")*(A1:C10=$I4))
F1	F1	=SUMPRODUCT(D1:D10*(A1:C10=$I4))
D1:D2,D7:D10	D1	=SUBTOTAL(103,A1)

 

[mrblister]

Hi @Eric W, I just wanted to say a proper "thank you"! Your solution helped me out a lot; the second solution with the helper column was particularly useful.

Cheers!

 

[Eric W]

Happy to help!