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?

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

1 Answer 1

Reset to default
0 

=COUNTA(QUERY(IMPORTRANGE("16nnuzNRxkGGJm1EBVX7kBT2bfW12TYwSiuaLFQIA", 
 "Sheet1!A2:B"), "where Col2 matches '[email protected]'"))/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 protected]'")))," "))),"\d+")))))

0

Improve this answer
15
  • 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, 2019 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, 2019 at 13:27
  • Looks like I'm getting the answer "1" no matter what with that formula.
    – eegatlanta
    Mar 14, 2019 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, 2019 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, 2019 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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