2

I have a Google sheet that logs all submissions on my website of various contact forms. I need to know how many times each contact form has been submitted, and in which week they were submitted.

The goal is to have a Google sheet to look like this: (Each tab in the Google sheet is the same name of these forms)

Week Commencing | Number of Submissions
  1st July 2019 | 37
  8th July 2019 | 7

The data I have looks like this: (in a tab named "data")

form name                         | date 
Custom Printed Premium Gift Boxes | 7/4/2019 12:01:28

Link to google sheet: https://docs.google.com/spreadsheets/d/1ffOVvGT7CGV3gXR1HP5kp6Pg1JgD40qRjlt6lwwFbo0/edit?usp=sharing

My questions:

  1. How do I count the submissions from the data tab and put the total, by the week, into the correct tab?
  2. How do I get the week number, convert it to a correct row title of "1st July 2019" and then have the number of submissions for that very week go into the correct cell?
  3. Is there a better way to achieve what I'm doing? Am I square peg/round hole-ing?
0
  • to derive week number:

    =ARRAYFORMULA(IF(LEN(B2:B), WEEKNUM(B2:B, 2), ))

    0


  • to convert it into the 1st day of each week number:

    =ARRAYFORMULA(IF(LEN(B2:B), VLOOKUP(WEEKNUM(B2:B, 2), 
     {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                               DATEVALUE(MAX(B2:B)))), 2),
         TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                               DATEVALUE(MAX(B2:B)))), "d. mmmm yyyy")}, 2, 0), ))

    0


  • to create full summary table:

    =ARRAYFORMULA(QUERY(IF(LEN(B2:B), {VLOOKUP(WEEKNUM(B2:B, 2), 
     {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                               DATEVALUE(MAX(B2:B)))), 2),
         TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                               DATEVALUE(MAX(B2:B)))), "d. mmmm yyyy")}, 2, 0), A2:A}, ),
     "select Col2,Col1,count(Col1) 
      where Col1 is not null 
      group by Col1,Col2 
      label count(Col1)''"))

    0


  • to put it into correct tab (with script - manual recalculation):

    function sheetName() {
    return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
    }

    =ARRAYFORMULA(QUERY(QUERY(IF(LEN(data!B2:B), 
    {VLOOKUP(WEEKNUM(data!B2:B, 2), 
     {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                               DATEVALUE(MAX(data!B2:B)))), 2),
         TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                               DATEVALUE(MAX(data!B2:B)))), 
     "d. mmmm yyyy")}, 2, 0), data!A2:A}, ),
     "select Col2,Col1,count(Col1) 
      where Col1 is not NULL 
      group by Col1,Col2 
      label count(Col1)''", 0), "select Col2,Col3 where Col1='"&SHEETNAME()&"'", 0))

    0


  • to put it into correct tab (without script - manual input)

    =ARRAYFORMULA(QUERY(QUERY(IF(LEN(data!B2:B), {VLOOKUP(WEEKNUM(data!B2:B, 2), 
    {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                           DATEVALUE(MAX(data!B2:B)))), 2),
     TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                           DATEVALUE(MAX(data!B2:B)))), 
    "d. mmmm yyyy")}, 2, 0), data!A2:A}, ),
    "select Col2,Col1,count(Col1) 
    where Col1 is not NULL 
    group by Col1,Col2 
    label count(Col1)''", 0), "select Col2,Col3 where Col1='Contact Form'", 0))

    0

  • 1
    This is tremendous! Thanks very, very much :-) – Tjobbe Jul 5 at 9:00

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.