?
Solved

Excel STOP COUNT/SUM on hidden rows

Posted on 2011-03-10
4
Medium Priority
?
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
New style of hardware planning for Microsoft Exchange server.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

752 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