The title pretty much sums the query up. I have a table of data with formulas (eg COUNTIF ) applied to each column. When I filter the column, by date range or region, say, I would like the formula to return the result just for the visible (filtered) entries. This is the same concept as the SUBTOTAL formula, which provides this functionality but only on a limited number of functions (SUM, AVG,MAX etc).
Any help solving this would be much appreciated.
Thanks
Sandra
SUBTOTAL() has a great many functions:
I believe the 100-level codes are not available in Excel 2000, but all the others are, including count.
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Look at the SUBTOTAL function
Given you already make mention of the SUBTOTAL function I presume you know what you can and can't do with it - if you want to do conditional calculations on visible rows (ie COUNTIF) then you're looking at using SUBTOTAL embedded within SUMPRODUCT (or using helpers) - we'd need more info to provide more assistance - ie what are you doing exactly ? Post a sample file if you feel it will help illustrate the issue(s) at hand.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
If you want to count the "x"s in A2:A100....but only on rows visible after filtering then try this formula
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100="x")+0)
Hi DaddylonglegsOriginally Posted by daddylonglegs
I'm new to using the forum and am not sure of the protocol/procedures for posts and replies etc. but I have tried the SUMPRODUCT formula you suggested and it has worked which is Brilliant! I am keen to understand the structure of the formula and what it is essentially doing - it seems to be based around the SUBTOTAL function but I am not sure about the OFFSET part and the A2 ROW..... part and why you need the SUMPRODUCT bit too? If you are able to give me any insights, this will really help with future projects. Many Thanks for your asssistance. Sandra
This part
SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))
returns a 1 for each row in the range if it's visible after filtering, otherwise a zero.
SUMPRODUCT is a standard way to count with multiple criteria, in this case your 2 criteria are whether the cell is visible....and whether it contains "x". See an exhaustive explanation of SUMPRODUCT here, one of the examples (example 11) shows a version of this type of formula....
The SUBTOTAL/OFFSET part is now a relatively well known "idiom", credited to Laurent Longre, I believe. See here for more
Hii Daddylonglegs,
Thanks for your post...it was really useful. However, refering back to your equation SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),A2:A100="X")+0). This equation works okay as long as the row A2 shows up in the visible filtered range. When a new criteria is selected and A2 is no longer visible, this equation returns an error message. Is there a way of making this equation dynamic?
Last edited by desadarius; 07-14-2012 at 10:12 AM. Reason: Typo
this is an old post but...it should work ok but you need 103 not 3 things changed in excel 2003> see post #2
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks