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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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

Forum statistics

Threads
1,226,515
Messages
6,191,495
Members
453,659
Latest member
thomji1

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