Hi it’s me again.

I have managed to get that work for you.

The formula is:

```
=ARRAYFORMULA(VLOOKUP(UNIQUE(FILTER('Original Data'!$B$2:B,'Original Data'!$B$2:$B<>"")),SORT(QUERY('Original Data'!$A$2:$D,"SELECT B, MAX(A), SUM(D), COUNT(C) WHERE A IS NOT NULL GROUP BY A,B ORDER BY B LABEL MAX(A)'',SUM(D)'',COUNT( C )''",-1),2,FALSE),{2,1,3,4},FALSE))
```

Sorry it’s a bit long but let me explain the process, from inside to outside.

`QUERY('Original Data'!$A$2:$D,"SELECT B, MAX(A), SUM(D), COUNT (C) WHERE A IS NOT NULL GROUP BY A,B ORDER BY B LABEL MAX(A)'',SUM(D)'',COUNT(C)''",-1)`

Returns this, it’s just the date, test, total paid and customer count group by the date and test in each row.

Next, I wrapped this inside a sort formula, sort the date in a descending way.

`SORT(QUERY('Original Data'!$A$2:$D,"SELECT B, MAX(A), SUM(D), COUNT(C) WHERE A IS NOT NULL GROUP BY A,B ORDER BY B LABEL MAX(A)'',SUM(D)'',COUNT(C)''",-1),2,FALSE)`

This sort must be done so the VLOOKUP can work the right way.

Next, setup the VLOOKUP. A pseudo-code of this would look like

VLOOKUP(Filter all unique values from the test column in original sheet that are not blank, The range of values that has been queried and sorted, Returns 4 columns in the right order that you want, Indicates a FALSE value so that the formula knows the 1st column in the range is not sorted)

How it works:

- Takes all unique non-blank values from test column: Test 1, Test 2, Test 3, Test 4.
- Look them up in the queried and sorted range, it’s sorted by latest date first so the returning first match (as VLOOKUP behaves) would be exactly what you want - the latest date for the corresponding test.
- Wrap them in an ARRAYFORMULA so it works for all test values to be looked up.

The copied sheet link is here.