Using two rows as column headers?

asked 2021-02-12 11:18:15 +0200

zimon gravatar image

updated 2021-05-20 08:19:53 +0200

Alex Kemp gravatar image

I would want to use AutoFilter feature to select and sort by different columns in LibreOffice Calc. I would want columns have headers AND subtotals in the same column and sorting via AutoFilter would not sort nor move TOTAL row.

When having new table:

A, B, C
id, count1, count2
...(data rows)

Adding TOTAL row (would want it to be the last row, but could also be the second row):

A, B, C
id, date, count
TOTAL, =subtotal(9,B:B), =subtotal(9,C:C)
...(data rows)

Now when making Main_menu > Data > Auto_filter it asks if the first row is wanted to be used as column headers. Yes, but I would like to have TWO ROWS as column headers.

When now sorting ascending or descending, it will move also values from the row 2. If some other function would be used in the subtotal, like COUNT (2), MAX (4) or MIN (5), the TOTAL row can be anywhere in the middle of rows after sorting.

So is there any way to define column headers to have two rows? Or somehow protect rows from AutoFilter sort?

I have tried to search answers, but haven't found yet. Also the included Help(F1) is rather poor. It seams not to have even those function numbers of subtotal function.

That information should also be in Function Wizard when subtotal is selected: "SUBTOTAL(Fucntion, Range) - Calculates subtotal in a spreadsheet" could be: "SUBTOTAL(Fucttion, Range) - Calculates subtotal in a spreadsheet (1=average,2=count,3=counta,4=max,5=min,6=product,7=stdev,8=stdevp,9=sum,10=var,11=varp)"

edit retag flag offensive close merge delete

Comments

1

Just use:

A, B, C
1 TOTAL, =subtotal(9,B:B), =subtotal(9,C:C)
2 id, date, count
3 ...(data rows)

and start AutoFilter in row 2

Opaque gravatar imageOpaque ( 2021-02-12 11:27:15 +0200 )edit

Select the data rows (from the third row down), and when asked "Do you want the first line to be used as column header?" click No.

LeroyG gravatar imageLeroyG ( 2021-02-12 12:44:17 +0200 )edit

Or in Opaque's example leave a blank row between the Totals row and the Headers row and then use the entire automatic data range with column headers again.

erAck gravatar imageerAck ( 2021-02-13 00:31:48 +0200 )edit