1

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

enter image description here

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

enter image description here

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).

  • I fell into a bit of an XY problem trap here, if anybody has a better suggestion for a title, please feel free to edit. – Jason C Feb 20 '17 at 14:55
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.

  • Based on your approach I was able to do it in one formula with =FILTER(UNIQUE(Data!A2:A),UNIQUE(Data!A2:A)<>""). – Jason C Feb 20 '17 at 14:48
1

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.

  • Well that just blew my mind. So many good options here. I didn't even know QUERY was a thing, so cool. I didn't consider the option of recalculating the totals. I'm going to accept Sherin Binu's answer since that one helped me arrive at a solution for column A only, which may be more helpful in the general case, but this is great. – Jason C Feb 20 '17 at 14:48
1

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.

  • 1
    Another thing I had no idea you could do. Tried with great success (test sheet, "Pivot Table 1"). I think I like this as the best solution to summary tables like this in general. Now I'm not sure which answer to accept! – Jason C Feb 20 '17 at 15:08
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.