Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Lisa
 
Posts: n/a
Default SUMPRODUCT TO CALCULATE VISIBLE CELLS ONLY

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?

  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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),--(D2:D10))

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

  #3   Report Post  
Lisa
 
Posts: n/a
Default

Debra, I'm sorry, I did not put the full formula that I am having a
problem with. It is:
SUMPRODUCT(B8:B250,D8:D250)/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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

You still need the Longre idiom Debra invokes...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B8:B250,ROW(B8:B250)-MIN(ROW(B8:B250)),,1)),
B8:B250,D8:D250)/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,D8:D250)/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.

  #5   Report Post  
Lisa
 
Posts: n/a
Default

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct on filtered cells Ndel40 Excel Worksheet Functions 19 January 20th 05 11:17 PM
How do I calculate ratios between 2 cells? Excel Novice Excel Discussion (Misc queries) 2 January 17th 05 04:29 PM
Excel - formula to calculate colored fill cells within a range wi. MA Excel Worksheet Functions 1 January 7th 05 05:06 PM
Strange Problem with Chart and Plot Visible Cells Option Marcus Leon Charts and Charting in Excel 1 January 7th 05 04:52 AM
Sumproduct ... Empty Cells vs Spaces? Ken Excel Discussion (Misc queries) 9 December 17th 04 09:03 PM


All times are GMT +1. The time now is 10:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"