Data Ranges

[ChuckF]

I created a simple spreadsheet
A1=1
A2=2
A3=3
A4=4
A5=5
A6=6
A7=7
A8=8
A9=9
A10=10

I create two data ranges, we will say A1 through A4 and call it MONDAY,
and the other A5 through A10 and call it TUESDAY.

Below I create the simple formula =sum(MONDAY) and the formula gives me
the solution of 10.
I also create another simple formula =sum(TUESDAY) and get a solution
of 45. So far...so good.

When column 'A' gets sorted a different way, my data ranges give
different values. (i.e. sort column 'A' descendingly and you get Monday
= 34 and Tuesday =21.

Does anyone know of a way to stop this from happening? Regardless how
the data is sorted, or filtered I would still like the value of Monday
to be 10.

Please let me know if anyone has any ideas.

 

[ChuckF]

Anyone?

 

[Herbert Seidenberg]

Here is one far out, impractical solution:
Add a helper column (Seq), so your data looks like this:
Seq Mon
1 1
2 8
3 5
4 6
5 3
6 8
7 1
8 3
9 7
10 4
Tue

Name the Seq, Mon and Tue ranges.
The sum for Mon is this array formula
=SUM((Seq<=MAX(ROW(Mon)-ROW(INDEX(Seq,1))+1))*Mon:Tue)
For Tue:
=SUM((Seq>MAX(ROW(Mon)-ROW(INDEX(Seq,1))+1))*
(Seq<=MAX(ROW(Tue)-ROW(INDEX(Seq,1))+1))*Mon:Tue)
The data may be sorted without affecting the results if
Seq is included in the sort.