1

Basically, trying to figure out how I can Sum the totals column based on the latest/max date, by town, ie filtered by unique and the latest date for each row.

Date Town Totals
September 5 Loerie 9
November 8 Loerie 4
May 7 Flower 2
February 2 Holo 8
May 9 Holo 7
July 23 Flower 3
June 7 Dump 1
March 3 Tzaneen 9
September 2 Tzaneen 4
April 3 Coffee 7

Able to unique sort the town list, and show the totals for each based on max date with =maxifs(C$2:C,B$2:B,F2,A$2:A,maxifs(A$2:A,B$2:B,F2))

Need to be able to sort and sum those results in a single function, but unsure how. Arrayformula? Shared the example doc. https://docs.google.com/spreadsheets/d/1SSNJJOoz1-pxVH0ZoFFZqChhZxjqtRz5dfvyQu76ueI/edit?usp=sharing

player0
  • 124,011
  • 12
  • 67
  • 124
Morgol
  • 13
  • 2

1 Answers1

0

try:

=QUERY(SORTN(SORT(A2:C, 2, 1, 1, 0), 9^9, 2, 2, 1), "select Col2,Col3")

with total:

={QUERY(SORTN(SORT(A2:C, 2, 1, 1, 0), 9^9, 2, 2, 1), "select Col2,Col3"); 
 "Total:", SUM(INDEX(SORTN(SORT(A2:C, 2, 1, 1, 0), 9^9, 2, 2, 1),,3))}

only total:

=SUM(INDEX(SORTN(SORT(A2:C, 2, 1, 1, 0), 9^9, 2, 2, 1),,3))
player0
  • 124,011
  • 12
  • 67
  • 124
  • =SUM(INDEX(SORTN(SORT(A2:C, 2, 1, 1, 0), 9^9, 2, 2, 1),,3)) works great thanks, can adjust it to other columns as need be. Any idea how to ignore empty "total" entries and sum the last non blank one? – Morgol Sep 28 '21 at 07:45
  • @Morgol not rly sure what are you after with "how to ignore empty "total" entries and sum the last non blank one?" can you expand on it a bit more? – player0 Sep 28 '21 at 20:05
  • There's several columns with their own totals, so the current function you provided works great overall for that. I'm trying to sum the column based on latest date IF that row isn't empty, else uses previous date. Explaining a bit stupidly here. 1 Jan ExampleA – Morgol Sep 28 '21 at 22:08
  • In the example sheet, "C List", currently it adds 0 for Loerie, the blank cell, since it's the latest date. Since that cell is blank I want to use the entry that's not blank, ie the previous date. So that total would be 5 not 4. – Morgol Sep 28 '21 at 22:16
  • @Morgol try in E14: `=SUM(INDEX(SORTN(SORT(FILTER($A2:$E13, E2:E13<>0), 2, 1, 1, 0), 9^9, 2, 2, 1),,COLUMN()))` – player0 Sep 28 '21 at 22:54
  • 1
    Works perfectly thanks a lot. Just needed to change the COLUMN() to the correct column and shift the filter ranges up as necessary. Thanks again – Morgol Sep 29 '21 at 07:01