If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMPRODUCT TO CALCULATE VISIBLE CELLS ONLY
There's an example here that counts visible cells in a filtered column.
You could modify the SUMPRODUCT formula to reference your cells, and add one more argument that refers to the cells to be totalled. http://www.contextures.com/xlFunctions04.html#Visible In the above example, the modified formula: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-MIN(ROW(A2:A10)),,1)), --(A2:A10=A12),--(D210)) would total the visible cells in column D. Lisa wrote: I have a formula at the top of a columnn to calculate the SUMPRODUCT. However, I need the SUMPRODUCT to recalculate the visible cells only when I filter the column. I don't think that the SUBTOTAL function will work. Any suggestions? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#2
|
|||
|
|||
Debra, I'm sorry, I did not put the full formula that I am having a
problem with. It is: SUMPRODUCT(B8:B250,D8250)/SUM(B8:B250). I want this formula to recalculate and give me the result of the visible cells only when I filter. How would I modify the formula you gave above to work with this? thanks for your help. |
#3
|
|||
|
|||
You still need the Longre idiom Debra invokes...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B8:B250,ROW(B8:B250)-MIN(ROW(B8:B250)),,1)), B8:B250,D8250)/SUBTOTAL(9,B8:B250) Lisa wrote: Debra, I'm sorry, I did not put the full formula that I am having a problem with. It is: SUMPRODUCT(B8:B250,D8250)/SUM(B8:B250). I want this formula to recalculate and give me the result of the visible cells only when I filter. How would I modify the formula you gave above to work with this? thanks for your help. |
#4
|
|||
|
|||
Thanks Aladin & Debra. I just started a new job that is heavy in Excel
and I am a relatively new user, so your reponses were greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
IF COUNTIF & COUNTA on Filtered Visible Cells | Tinä | General Discussion | 2 | November 1st, 2004 08:04 PM |
Calculate total number off cells with data in | Gazza | New Users | 4 | June 7th, 2004 05:52 PM |
Slanting Cells in first Row | Worksheet Functions | 14 | February 27th, 2004 06:47 PM |