Filter excluded rows in array formula

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
211
Office Version
  1. 2021
Platform
  1. Windows
I have an array formula. This is a simplified example of my formula:

Excel 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:
Excel Formula:
{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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try:

Book1
ABCDEF
1xxx1111
2
3xx
4
5xx
6xx
7x
8xx
9
10x
Sheet1
Cell Formulas
RangeFormula
E1E1=SUMPRODUCT((A1:A10<>"")*(A1:C10=$I4))
F1F1=SUMPRODUCT(SUBTOTAL(103,OFFSET(A1,ROW(INDIRECT("1:10"))-1,0,1,1))*(A1:C10=$I4))


And with a filter:

Book1
ABCDEF
1xxx115
2
7x
8xx
9
10x
Sheet1
Cell Formulas
RangeFormula
E1E1=SUMPRODUCT((A1:A10<>"")*(A1:C10=$I4))
F1F1=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.
 
Upvote 0
Not that I'm aware of, even in the newest versions of Excel. You could use a helper column though:

Book1
ABCDEF
1xxx11111
20
3xx1
40
5xx1
6xx1
7x1
8xx0
90
10x1
Sheet1
Cell Formulas
RangeFormula
E1E1=SUMPRODUCT((A1:A10<>"")*(A1:C10=$I4))
F1F1=SUMPRODUCT(D1:D10*(A1:C10=$I4))
D1:D10D1=SUBTOTAL(103,A1)


Book1
ABCDEF
1xxx1115
20
7x1
8xx0
90
10x1
Sheet1
Cell Formulas
RangeFormula
E1E1=SUMPRODUCT((A1:A10<>"")*(A1:C10=$I4))
F1F1=SUMPRODUCT(D1:D10*(A1:C10=$I4))
D1:D2,D7:D10D1=SUBTOTAL(103,A1)
 
Upvote 0

Forum statistics

Threads
1,226,527
Messages
6,191,568
Members
453,665
Latest member
WaterWorks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top