Solved

Excel STOP COUNT/SUM on hidden rows

Posted on 2011-03-10
4
326 Views
Last Modified: 2012-05-11
I have a list of assets that we filter.

Without changing how we filter and move data around....

I want to be able to show the count and sum of value for each asset  in TOTAL as the filter changes.

I can get the right value with this:
=COUNT( IF(R_FilterType = "Desktop",1,0))
or
SUMIFS( R_filterType, "Desktop)

but It counts hidden rows as the filter does its work.

Is there a way to do it with subtotal or another approach.

Data would be:

Desktop  A $100    div1
Laptop   A $100    div2
Desktop  B $100   div2
Desktop C  #150   div2
Laptop D  $120   div1
etc

thanks experts
0
Comment
Question by:skillilea
4 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 250 total points
ID: 35099184
Use SUBTOTAL() instead, like this:

=SUBTOTAL(2, A1:A100)

This will count the number of visible rows on range A1:A100. This function can be used to sum, count, etc.

0
 

Expert Comment

by:pradeepprahllad
ID: 35104554
=subtotal(9,A1:A10) also works
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 35108162
Hello skillilea,

SUBTOTAL will work to sum/count a whole column, excluding filtered out rows, but I understand you want to sum/count with a condition, so SUBTOTAL on it's own won't be sufficient for that. Try this for a count

=SUMPRODUCT((R_FilterType=F2)*SUBTOTAL(3,OFFSET(R_FilterType,ROW(R_FilterType)-MIN(ROW(R_FilterType)),0,1)))

or for a sum

=SUMPRODUCT((R_FilterType=F2)*SUBTOTAL(9,OFFSET(Sum_Range,ROW(Sum_Range)-MIN(ROW(Sum_Range)),0,1)))

where E2 contains the criterion, e.g. Desktop

R_filterType and Sum_Range should be the same size and not the entire column

If you want the formulas to be simpler then you could use a helper column, e.g. in a blank column then use a formula like this in row 2 copied down

=SUBTOTAL(3,A2)

where column A is a column that will always be populated in every row.....and then you can use these formulas for count and sum respectively

=COUNTIFS(R_FilterType,F2,helper,1)

=SUMIFS(Sum_Range,R_FilterType,F2,helper,1)

see attached for both approaches

regards, barry
26878523.xlsx
0
 

Author Closing Comment

by:skillilea
ID: 35124214
Thanks experts....

well done witht the example!

sk
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question