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
CodePudding user response：
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))