Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel STOP COUNT/SUM on hidden rows

Posted on 2011-03-10
4
Medium Priority
?
334 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 1000 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 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This is an article on how to answer questions, earn points and become an expert.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

578 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