How to sum of filtered rows where condition applies?

Question

I am looking for an Excel formula which would produce the sum of all rows in a range, where a certain condition is met AND the rows are not filtered.

I know how to do each individually:

• Sum where a condition applies: =SUMIF(A1:A10,">=0") for all positives for instance
• Sum of all rows that are unfiltered/visible: =SUBTOTAL(9,A1:A10)

However, I am stumped on doing both at the same time. Is there any way to achieve this in a single formula without creating additional columns (no VBA)?

Answer 1

Try this formula

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A1,ROW(A1:A10)-ROW(A1),0)),(A1:A10>0)+0)

change the condition at the end as required