0

I have the next year mapped out (one line for each day) and have the week subtotal at the end of each week (which is a Friday).

I am wanting the Next weeks subtotal to show in another cell for that week. For example, now that we have passed the 25-Jan it will then show the next subtotal (being 1-Feb), then when the 2-Feb comes the 8-Feb will show:

Date       Daily Count    Weekly Count

21/01/19      5             
22/01/19      5             
23/01/19      5              
24/01/19      5             
25/01/19      5              25          
26/01/19      1              
27/01/19      1               
28/01/19                     
29/01/19                       
30/01/19                        
31/01/19                        
01/02/19                     2   
02/02/19     

Really have no idea how to do this one.

  • MARK, we came up with very different interpretations of what Woodie is looking for. Interesting. We shall see... – Erik Tyler Jan 27 at 3:01
0

Woodie. Assuming your Date column is A and your Daily Values column is B:

  1. Delete all of Column C including your header. The column must be entirely clear because an array formula will "take over" that column in the next step.

  2. Place the following formula into cell C1:

=ArrayFormula(IF(ROW(A:A)=1,"Weekly Total",IF(A:A="","",IF(WEEKDAY(A:A)<>6,"",IF(A:A>TODAY()+6,"",SUM(B:B)-SUMIF(ROW(A:A),"<="&IFERROR(ROW(A:A)-7,1),B:B)-SUMIF(ROW(A:A),">"&ROW(A:A),B:B))))))

HOW IT WORKS:

=ArrayFormula(...)

This "wrapper" tells Google Sheets to treat the formula differently that regular formulas. Where regular formulas return one result, an array formula processes an entire range (in your case, all of Column C). It's important to keep in mind with array formulas that all ranges being considered in computations within the formula must be the same size.

=ArrayFormula(IF(ROW(A:A)=1,"Weekly Total",...))

As the array considers all of the column, if it assesses that the current row is Row 1, it should leave the header "Weekly Total." I could have used any column letters as the ROW() parameter and it would have worked the same; I just tend to use the first column whose data will be considered in the formula, which in this case is Column A.

=ArrayFormula(IF(ROW(A:A)=1,"Weekly Total",IF(A:A="","",...)))

If it is not the first row, check to see if the corresponding cell in Column A is blank. If it is, also make the corresponding cell in Column C blank.

=ArrayFormula(IF(ROW(A:A)=1,"Weekly Total",IF(A:A="","",IF(WEEKDAY(A:A)<>6,"",...))))

If it is neither the first row nor a row where Column A is blank, we know the corresponding cell in A contains one of your dates. If the WEEKDAY of any date does not equal 6 (counting Sunday as 1, Friday is 6), also leave it blank.

=ArrayFormula(IF(ROW(A:A)=1,"Weekly Total",IF(A:A="","",IF(WEEKDAY(A:A)<>6,"",IF(A:A>TODAY()+6,"",...)))))

If it is not the first row, or a row where A is blank or a non-Friday day, we're on a Friday. Now check to see if the date in Column A is more than six days away from today (i.e., past next Friday). If so, keep those future-date cells blank as well.

=ArrayFormula(IF(ROW(A:A)=1,"Weekly Total",IF(A:A="","",IF(WEEKDAY(A:A)<>6,"",IF(A:A>TODAY()+6,"",SUM(B:B)...)))))

If the cell in Column C is not in Row 1, not across from a blank cell in A, and not across from a non-Friday day, we need to do a calculation. The first part of the calculation will be to take the entire sum of numbers from Column B. From this, we're going to subtract (or "rule out") certain amounts.

=ArrayFormula(IF(ROW(A:A)=1,"Weekly Total",IF(A:A="","",IF(WEEKDAY(A:A)<>6,"",IF(A:A>TODAY()+6,"",SUM(B:B)-SUMIF(ROW(A:A),"<="&IFERROR(ROW(A:A)-7,1),B:B)...)))))

Things get tricky here.

SUMIF(ROW(A:A),"<="&IFERROR(ROW(A:A)-7,1),B:B)

That's the isolated SUMIF.

SUMIF(ROW(A:A),"<="...ROW(A:A)-7...)

SUMIF usually takes a standard range. But we can also ask it to consider row numbers as a special range, which is what's happening here. In English, this part says, "Check all row numbers and flag only those that are less than or equal to seven rows before the current row."

In short, that's going to be everything from "last Friday" and earlier. However ...

Your first week of the year is partial. There are not 7 days before the first Friday, so we have to control for this or we'll get an error:

SUMIF(ROW(A:A),"<="&IFERROR(ROW(A:A)-7,1)...)

By wrapping the condition in IFERROR, we can tell the formula, "If you run into any problems, just use this value instead." This IFERROR control exists solely for that first week in January, in which case we don't let the formula try to go any further back than Row 1 (that's the 1 after the comma).

SUMIF(ROW(A:A),"<="&IFERROR(ROW(A:A)-7,1),B:B)

Together, this says, "Make a group of all values in Column B that occur more than seven rows (i.e., days) ago (or, in the case of the first week of January, just don't throw me an error)."

Back to the putting this into the entire formula:

=ArrayFormula(IF(ROW(A:A)=1,"Weekly Total",IF(A:A="","",IF(WEEKDAY(A:A)<>6,"",SUM(B:B)-SUMIF(ROW(A:A),"<="&IFERROR(ROW(A:A)-7,1),B:B)...)))))

If the array formula is not on the first row, not across from a blank A cell, and not across from some other day but Friday, start by summing all the numbers in Column B and then subtract any numbers earlier than seven rows (i.e, days) back. This leaves us with a total of only values in Column B from any Friday forward. But we still have to trim off some values.

=ArrayFormula(IF(ROW(A:A)=1,"Weekly Total",IF(A:A="","",IF(WEEKDAY(A:A)<>6,"",IF(A:A>TODAY()+6,"",SUM(B:B)-SUMIF(ROW(A:A),"<="&IFERROR(ROW(A:A)-7,1),B:B)-SUMIF(ROW(A:A),">"&ROW(A:A),B:B))))))

Finally, trim off the sum of all numbers in Column B that fall in ROWs greater than (past) the current row (i.e., "after today").

This leaves us with only Fridays showing the sum of values from only the last seven days of Column B.

  • Erik, that is Fantastic!! Not only have you given me the formula, but you have also explained how it works - this is going to be very helpful!! I will need to re-look at the structure of the sheet (glad I have left plenty of time for planning - my new year starts 1-Apr, so I have time to make changes). – woodie OS Jan 27 at 3:19
  • Great, Woodie. If this satisfies your need, would you mark my previous post with the formula and explanation as the correct answer? This will keep others from continuing to attempt to answer, and it makes it easier for future users to find answers to similar questions. – Erik Tyler Jan 27 at 3:25
  • I was truly unsure. Looks like your interpretation was on, @Mark. But hopefully, even if not what Woodie originally wanted, the addition array I provided will be of use and save him some time against doing it manually. Great work. – Erik Tyler Jan 27 at 5:02
0

=SUM(QUERY(QUERY({{A2:B}, {ARRAYFORMULA(IF(LEN(A2:A), WEEKNUM(A2:A, 16), ))}}, 
 "select *", 0), 
 "select Col2 
  where Col3 matches '"&WEEKNUM(TODAY(), 16)&"'", 0))

6



5

  • This one is great! As I have ended up needing to put the data on another worksheet. If I wanted to do this to another dataset in the next column, what would I change? – woodie OS Jan 27 at 3:17
  • @woodieOS another Sheet: =SUM(QUERY(QUERY({{Sheet1!A2:B},{ARRAYFORMULA(IF(LEN(Sheet1!A2:A), WEEKNUM(Sheet1!A2:A, 16), ))}}, "select *", 0), "select Col2 where Col3 matches '"&WEEKNUM(TODAY(), 16)&"'", 0)) – user0 Jan 27 at 3:22
  • @woodieOS another spreadsheet: =SUM(QUERY(QUERY({{IMPORTRANGE("URL-OR-ID", "Sheet1!A2:B")},{ARRAYFORMULA(IF(LEN(IMPORTRANGE("URL-OR-ID", "Sheet1!A2:A")), WEEKNUM(IMPORTRANGE("URL-OR-ID", "Sheet1!A2:A"), 16), ))}}, "select *", 0), "select Col2 where Col3 matches '"&WEEKNUM(TODAY(), 16)&"'", 0)) – user0 Jan 27 at 3:25
  • If I had data in C (no longer need the Weekly column), would this work to give me the answer from data in Column C? =SUM(QUERY(QUERY({{A2:C},{ARRAYFORMULA(IF(LEN(A2:A), WEEKNUM(A2:A, 16), ))}}, "select *", 0), "select Col3 where Col4 matches '"&WEEKNUM(TODAY(), 16)&"'", 0)) – woodie OS Jan 27 at 3:33
  • How the heck do you upload a screenshot? – woodie OS Jan 27 at 3:49
0

SOLVED

Thank you to Erik

Erik's response was very detailed and very helpful if I ever needed an option like this down the track, ended up using the other one due to the current format of my Sheet

Ended up using the following formula as the data would be used on another sheet (that I would monitor daily as there are 59 different data sheets in the project)

=SUM(QUERY(QUERY({{A2:B},{ARRAYFORMULA(IF(LEN(A2:A), WEEKNUM(A2:A, 16), ))}}, 
 "select *", 0), 
 "select Col2 
  where Col3 matches '"&WEEKNUM(TODAY(), 16)&"'", 0))

Thanks again, spent hours on this and solved in no time thanks to the aforementioned personal

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.