Hello there
i have slight issue with a sum product calculation enclosed - issue is I need to filter on column B ( RANGE ) and have sumproduct calaculation apply to the filter only
is this possible ?
many thanks
peterSumProductIssue.xls
Hello there
i have slight issue with a sum product calculation enclosed - issue is I need to filter on column B ( RANGE ) and have sumproduct calaculation apply to the filter only
is this possible ?
many thanks
peterSumProductIssue.xls
Same question asked here
though dont ask me how they came up with the solution
http://www.pcreview.co.uk/forums/sum...-t1770074.html
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
blimey ..........which one am i using ? that only appears to apply to one column whereas I need to apply to two ?
Hi,
Does the attached help. I've added a small sub to work out which value is being filtered.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
put this in cell D5:
whenever you filter on column B (Range) or column C (Item), the value in D5 will automatically change.=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$6,ROW($B$7:$B$18)-MIN(ROW($B$7:$B$18))+1,0))*$D$7:$D$18*$E$7:$E$18)
Last edited by icestationzbra; 11-27-2012 at 12:02 PM.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
Another little shorter version.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6,ROW(B7:B18)-ROW(B6),,1)),D7:D18,E7:E18)
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks