[Solved] Sum sorted data which will be un-sorted

[John_Ha]

See Test.ods where I want to sum all Fred values in Col C, and seperately sum all the Mary values in Col C. When I un-sort back to the original order, I want the sums still to be correct.

I add Col A as a sequence on which I can sort and later un-sort the data back to its original order.
.

1.png (16.94 KiB) Viewed 382 times

.
A simple way to do it is

1. Create the helper columns Fred and Mary.
2. Mark which are Fred and which are Mary with a 1
3. Sort the data on Col Fred (or Mary).

I now have all the Fred values and all the Mary values "adjacent" so I can sum them with =SUM(C4:C8) and =SUM(C9:C13).
.

2.png (16.9 KiB) Viewed 382 times

.
The problem is, as expected, the sums go wrong when I un-sort the data back into its original order.

Two questions:

1 I am sure there is a (much!) better way to do which will survive the un-sort. I suspect it's pivot tables.

2 If not, is there a simple way automatically to convert the expression =SUM(C4:C8) to =C4+C5+C6+C7+C8? The latter expression will survive the un-sort. Needless to say, the real application has hundreds of rows.

[John_Ha]

An alternative way is to copy the Fred values into the Fred column by =IF(B1="Fred";C1;"") and then sum the column. My slight concern is it then depends on the names all being identical.

[RPG]

I think the most easy method is to use a pivot table. Below I describe the steps I did.
I did use LibreOffice there ApacheOffice does not work on my computer. LibreOffice does automatic insert a new sheet but ApacheOffice does insert the pivot table on the same sheet.

Steps
I have add a column-title "Name" to the names.

Select the columns "Name" and "Value"
menu --> Data -- PivotTable --> Add or Edit

Drag
The word Name to Rowfields
the word Value to the datafield
press OK

[MrProgrammer]

I want to sum all Fred values in Col C, and seperately sum all the Mary values in Col C.

This attachment shows three ways to get the Fred and Mary totals:
• Pivot Table   (formerly called Data Pilot)      (No formulas needed; Easiest method)
• The SUBTOTAL function and Autofilter        (No helper columns needed)
• [Tutorial] The SUMPRODUCT function          (For your helper columns)
The subtotal shows the values selected by Autofilter. No sorting is needed for these three methods. The rows can be in any order and the Fred and Mary totals will not change.

202505210835.ods

(15.66 KiB) Downloaded 3 times

The problem is, as expected, the sums go wrong when I un-sort the data back into its original order.

Sorting rearranges the data but does not change your formulas =SUM(C4:C8) and =SUM(C9:C13), as explained in paragraph If you rearrange rows using Data → Sort. Sorts never change formulas which refer to cells in the sort range. It can't, as explained in [Solved] Formulas Don't Adapt to Sorting Their References.

is there a simple way automatically to convert the expression =SUM(C4:C8) to =C4+C5+C6+C7+C8?

No, consider the case of =SUM(C4:C8888). Changing that to =C4+C5+…+C8887+C8888 would create a formula with over 8000 terms. But Calc does not support formulas that large. Even if you do change =SUM(C4:C8) to =C4+C5+C6+C7+C8, Calc won't update the latter when you sort the data.

My slight concern is it then depends on the names all being identical.

An AutoFilter (or StandardFilter) is a quick way to check for typographical errors in a small set of names. A pivot table can do that too. Create one, check the data, then delete it. If no errors are present this will take less than a minute.

ApacheOffice does insert the pivot table on the same sheet.

OpenOffice pivot tables provide More → Results to → New Sheet. I find it more convenient to have my pivot tables on separate sheets, though I didn't do that for this topic.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Alex1]

See the attachment.

[John_Ha]

Thanks all!

[Villeroy]

Aggregations (sum, min, max, count, average etc.) should never depend on any specific sort order. download/file.php?id=15673 demonstrates aggregation by SUBTOTAL, SUMPRODUCT, DSUM, DCOUNT, DAVERAGE and a data pilot.
In order to play with SUBTOTAL and DSUM, put the cell cursor in the source list, then call menu:Data>Filter>Advamced Filter and pick a named criteria range from the left list box. The D-functions and SUBTOTALs on the second sheet will show the aggregates of the filtered range.