Calculate visible cells only?

K

Ken

Excel2003 ...

Row 1 ... Cols D-E-F-G-H-I-J ... contain SUMIF Formula with Range (D4:D100)
changed for each respective Column. Works great when Rows not Filtered.

Cell D1 contains ... =SUMIF(D4:D100,">0",K4:K100)
Cell E1 contains ... = SUMIF(E4:E100,">0",K4:K100)
etc

Row 2 (same Cols) contains SUBTOTAL Formulas (no issue).

Row 3 contains my Filter Switches

Issue ... Is there a way to write formula in Row 1 so it too will only
return the value of the Filtered rows as does SUBTOTAL?

Thanks ... Kha
 
T

T. Valko

Enter this formula in D1 and copy across to J1:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D4:D100,ROW(D4:D100)-ROW(D4),0,1)),--(D4:D100>0),$K4:$K100)
 
K

Ken

Works exactly as requested ... Thank you ... Kha

T. Valko said:
Enter this formula in D1 and copy across to J1:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D4:D100,ROW(D4:D100)-ROW(D4),0,1)),--(D4:D100>0),$K4:$K100)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fill down visible cells only 4
Macro Pause to Select range then Subtotal 0
If Formula (visible cells only) 0
How to check for blank cells with formula 3
format cells in one column based on value in another column 3
Copy and Paste Macro 5
How to search for nearest nonblank cell in previous rows? 8
Formula to Count and Return Most common Value in a Dynamic Named Range 1

Top