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.
