Solved

Excel STOP COUNT/SUM on hidden rows

Posted on 2011-03-10
4
328 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 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

To Patch or not to Patch? That is the question!

Don't get caught out like thousands of others around the world in the recent Ransomware Fiasco!
Discuss..
- Why it's not a good idea to wait before Patching
- Sensible approaches to Patching discussed
- Add your feedback, comments and suggestions

Question has a verified solution.

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

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
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…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

734 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