Getting a count and sum total for the latest date by category with Form submission

Ask for Help
1

My original data looks like this:
Original Data

I’m looking for a way for my query to return information ONLY for the latest date associated with each Test. For that date, I am looking to get the count number of customers and the $ Paid total. What’s complicating my effort is the fact that multiple people could take the Test at a given date and across dates.

The ideal results should look like something like this: Ideal Results

I am getting information submitted into this table via Forms in real-time hence row range will be dynamic & need a solution that can give me the info I am looking for at any given time.

Spreadsheet link for Original data and the ideal results: Google Spreadsheet with Original Data

I would really appreciate any insights or help from anybody.

2

Hi it’s me again.

I have managed to get that work for you.

image
image1170×309 38.1 KB

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.

image

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)

image

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.

3 Likes
3

@ThinhDinh, thank you so much for the detailed analysis and solution. This is helping me get some critical aggregated data that I otherwise would not have so I really appreciate the time help.

1 Like
4

No worries Fassil, let me know if there is anything I can help!