Value of cell above and to the left of matching cells

Question

In Google Sheets, I have a sheet that looks like this that I cannot modify:

My goal is to create, through formulas, a sheet that looks like this:

Where that "summary" sheet displays the value for each row labelled "TOTAL" as well as the date.

The "Total" column in the summary sheet I got with no problems using (where Data is the name of the original sheet):

=FILTER(Data!C:C,Data!B:B="TOTAL")

But I can't figure out the "Date" column, because the date is in a different row than the word "TOTAL" (it's above it).

So my question is: How can I extract that date above and to the left of each cell containing "TOTAL"? Ideally I'm trying to do it with an array formula (to keep the summary sheet simple), and with no blank rows in the resulting array (to keep the Date column lined up with the Total column without much hassle). I'd prefer not to write a script.

The only thing I've thought of to try so far is a bunch of convoluted formulas and temporary columns involving match, indirect, and address, and it was neither convenient nor correct. It was only a mess and a failure.

I've put together a sample document here (contains "Data" and "Summary" sheets).

Answer 1

Well, I've solve this, but by creating three additional rows. You can hide them later. You can access that spreadsheet here. First off, you gotta create a row (preferably named Count) that counts each row with unique number assigned to each entry using COUNTIF function. Then an TempDate column which is same as Date column in your Data sheet, but having it right next to Count column will help us later. Then Create a column named UniqueTemp which filters out duplicate entries in Count column. And then a Date column which uses a lookup function from your UniqueTemp column to the Date column. You then just add each Count entry in your Data sheet for every Date using sumif and then you got your work done. Hope this solves your problem.

Also, you could check out the spreadsheet linked here for formulas if I wasn't clear enough.

Answer 2

In A1 of a new sheet please try:

=query(Data!A:C,"select A, sum(C) where A is not null group by A")

with formatting to suit.

Answer 3

Another option is to create a pivot table with Date for Rows, Count for Values and Filter on What to exclude TOTAL. Then add labels to suit.

Answer 4

Sherin Binu's answer makes a clever suggestion of grabbing the unique values from the date column then removing the blank rows, via a few temporary columns.

Based on that approach, I looked up how to remove blank values from an array (UNIQUE alone leaves the blank values from column A in the array) and I've discovered a way to do that in a single array formula with no temporary columns:

=FILTER(UNIQUE(Data!A2:A),UNIQUE(Data!A2:A)<>"")

So this is the solution I'll be using. As I post this pnuts also posted a single-formula solution using QUERY, which does both columns in one formula, and the suggestion to use a pivot table for the whole summary sheet instead, which is super clean and doesn't involve any formulas. So, lots of good options here.