x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 337

# Excel STOP COUNT/SUM on hidden rows

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
skillilea
2 Solutions

Commented:

=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

Commented:
=subtotal(9,A1:A10) also works
0

Commented:
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 Commented:
Thanks experts....

well done witht the example!

sk
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.