I have an array formula. This is a simplified example of my formula:
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:
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 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 tried using SUBTOTAL:
Excel Formula:
{SUBTOTAL(9,(A1:A10<>"")*(A1:C10=$I4))}
Can anyone assist? Note the formula will be used on a system that doesn't have access to AGGREGATE function.