# Using two rows as column headers?

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 close merge delete

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

( 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.

( 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.

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