• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

ms excel sort

I just opened a question about counting the number of rows, it turned out that

=COUNTA(E1:E2000)

counts the rows as per the regmigrant's suggestion

Now I am having a problem with sorting. So I need to sort the excel doc, then total the rows.
But when I sort the rows, the numbers of the rows is all off, for example the numbering goes

2,3,6,7,15,16

as so on, obviously due to the filter, but my formula is

=COUNTA(E1:E2000)

my count is 2000 no matter how I filter the document.
So how can I get an accurate count of the rows after I apply the filter?
0
JeffBeall
Asked:
JeffBeall
  • 2
  • 2
2 Solutions
 
helpfinderCommented:
try this formula
=SUBTOTAL(3;E1:E2000)

(3 in the formula represents COUNTA)
0
 
JeffBeallAuthor Commented:
OK, I tried

=SUBTOTAL(3;E1:E2000)

but I get this message

"The formula you typed contains an error"

there are some suggestions, but it's just things like click help for more info.
0
 
MacroShadowCommented:
=SUBTOTAL(3,E1:E2000)
0
 
JeffBeallAuthor Commented:
I guess you had a typo helpfinder, using the comma after the 3 worked
Thank you so much! that works great.
0
 
helpfinderCommented:
it´s not a typo. if comma or semicolon is a separator depends on Regional Settings and what you have set.
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!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now