**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have a Heading Title at the top of each column and keep my tally (formula) of that column in the second row. However, ever time I sort/filter my worksheet the second row also gets sorted and I lost my tally - which I use for analysis in the second sheet. I have tried freeze and split and neither works. Please help !

Solved by F. Q. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
09/03/2018 - 06:31

Welcome

User
09/03/2018 - 06:32

Hello - can you help please ?

Excelchat Expert
09/03/2018 - 06:32

Yes, please send your sheet, if you have with you

User
09/03/2018 - 06:33

Can't upload sheet due to privacy concerns but I am typing up an example on the document preview to show the issue

Excelchat Expert
09/03/2018 - 06:33

Okay

User
09/03/2018 - 06:36

Okay - can you see the sheet ? If I now sort by Column E my second row summary will move

Excelchat Expert
09/03/2018 - 06:38

Yes, it will because when you will filter the values in column E by 1 then the 2nd row will hide

Excelchat Expert
09/03/2018 - 06:38

To avoid this

Excelchat Expert
09/03/2018 - 06:38

You need to put the summary cell in at the end of the table

Excelchat Expert
09/03/2018 - 06:39

and use SUBTOTAL function to get avoid this issue

Excelchat Expert
09/03/2018 - 06:39

ike

Excelchat Expert
09/03/2018 - 06:39

Like this

Excelchat Expert
09/03/2018 - 06:42

Now if I filter the column E by value 2 only then result will be as follows

Excelchat Expert
09/03/2018 - 06:43

Sorry, let me arrange the selected area for Filter

User
09/03/2018 - 06:44

I simply want to know at a glance how many seminars I have - so I want to be able to go to the top see Seminar and then directly underneath how many I have - is that possible ? Then when I sort by surname those 2 rows don't change

User
09/03/2018 - 06:45

Also I am constantly adding to the sheet

Excelchat Expert
09/03/2018 - 06:46

Alternately, you can keep the summary at top of Filtered Headers

Excelchat Expert
09/03/2018 - 06:46

Like this

Excelchat Expert
09/03/2018 - 06:47

See now on top of To Do column

Excelchat Expert
09/03/2018 - 06:47

Use this function to count the entries

Excelchat Expert
09/03/2018 - 06:47

=counta(E3:E)

Excelchat Expert
09/03/2018 - 06:48

Now whatever entry you will input in column E after row 3 of column E, it will count that entry to show you the results

Excelchat Expert
09/03/2018 - 06:49

You can use SUBTOTAL function here too

User
09/03/2018 - 06:49

If I then filter the whole sheet (eg Cntrl A) & then sort function it will always keep those two rows the same ?

Excelchat Expert
09/03/2018 - 06:50

Yes, you can sort or filter the table as per requirement

Excelchat Expert
09/03/2018 - 06:50

If you want to count the the selected rows only

Excelchat Expert
09/03/2018 - 06:51

then you can use SUBTOTAL function. This only counts or sum the values of Visible cells

Excelchat Expert
09/03/2018 - 06:51

Like this

Excelchat Expert
09/03/2018 - 06:52

see in preview window now

Excelchat Expert
09/03/2018 - 06:52

I am going to filter column E for 1 values only

User
09/03/2018 - 06:53

I have thousands of rows - the easiest is to select the whole sheet rather than picking the rows - will this be a problem ?

Excelchat Expert
09/03/2018 - 06:54

No, there would not be a problem

Excelchat Expert
09/03/2018 - 06:55

I am just giving you options to get rid of your problem

Excelchat Expert
09/03/2018 - 06:55

You can use COUNTA or SUBTOTAL functions as per your requirment

Excelchat Expert
09/03/2018 - 06:55

COUNTA will give you the count of all the cells in column E

Excelchat Expert
09/03/2018 - 06:56

But, SUBTOTAL will give you the count of visible cells of column E, when you will filter the values in column E

User
09/03/2018 - 06:56

The thing with that is I have to sort without the Heading - just by the Column E - I can live with that though !

Excelchat Expert
09/03/2018 - 06:58

You simply first select the table and then sort the data by column E, the summary cell will not be disturbed at all

User
09/03/2018 - 06:58

Yep that makes sense - thanks so much ! Have a great evening !

Excelchat Expert
09/03/2018 - 06:58

You are welcome

Excelchat Expert
09/03/2018 - 06:59

Please rate this session

Excelchat Expert
09/03/2018 - 06:59

Have a nice day ahead!

Excelchat Expert
09/03/2018 - 07:00

You may close this session, and rate at the end. Thanks

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*