0

I have two Google Sheets workbooks, Workbook A and Workbook B.

Workbook A is generated from a Google form that collects email addresses submitted from users. Workbook A has two columns next to each other, one with a timestamp (formatted with date and time like this: 3/11/2019 8:16:51) and the other with the email address.

I want to count the number of days each email address has been submitted. I want to put that number in Workbook B.

So here is the formula I put in Workbook B (edited for privacy):

=(SUM(COUNTIF(IF(IMPORTRANGE("https://linktosheetA", "Nameoftab!A:A")=TODAY(-1)))"emailIamlookingfor.com"))

I was going to change the email and put that formula in a cell for each person whose response rate I wanted to count.

I have two questions:

First, that formula doesn't work at all. Can it be fixed?

Second, is there a better way, overall, to accomplish what I'm trying to do?

  • if the same email is submitted 2x in the same day you want to count 2 or 1 ? – user0 Mar 12 at 22:23
0

=COUNTA(QUERY(IMPORTRANGE("16nnuzNRxkGGJm1EBVX7kBT2bfW12TYwSiuaLFQIA", 
 "Sheet1!A2:B"), "where Col2 matches 'x@mail.com'"))/2

0


=ARRAYFORMULA(COUNTA(UNIQUE(IFERROR(REGEXEXTRACT(TO_TEXT(IFERROR(
 SPLIT(TO_TEXT(IFERROR(QUERY(IMPORTRANGE("ID", "FormResponses1!A2:B"), 
 "select Col1 where Col2 matches 'email@address.com'")))," "))),"\d+")))))

0

  • I want to count one email submission per day. They may have multiple submissions in a day, but I just want to count one. – eegatlanta Mar 13 at 13:02
  • Here is a link to a dummy version of Workbook A (note that this dummy version is NOT connected to a Google Form, but the real version is): docs.google.com/spreadsheets/d/… And here is a dummy version of Workbook B: docs.google.com/spreadsheets/d/… – eegatlanta Mar 13 at 13:27
  • Looks like I'm getting the answer "1" no matter what with that formula. – eegatlanta Mar 14 at 13:58
  • So we need to count one email submission per day and count the sum of those submissions. So if someone has submitted information once per day for 7 days, we would need to count up to 7. It's a running total of how many times that has made a daily submission via the form. I apparently failed to remove all the private info - will you please dummify that email address in your answers? I greatly appreciate it. – eegatlanta Mar 14 at 17:45
  • done. the formula does exactly that what you request. pls double check the ID in IMPORTRANGE currently it imports from your real sheet not from the dummy sheet you set up. that could be the reason why 1 is never changing – user0 Mar 14 at 17:49

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.