< Go Back

Excel - COLUMN Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.