2

I'm a teacher and need help with a Google Form that collects the following info:

  • Timestamp
  • Name
  • Class
  • Lunch option

All of the data comes into a Google sheet. I'm using successfully using the QUERY function to get the data into separate sheets based on Class as I want to keep the data long-term for attendance and Timestamp info.

I have another sheet where I would like to show a count for each Lunch choice by Class and only for the current date.

Here's how the data is laid out in the 'Form Responses' sheet:

A B C D                                                         
1 Timestamp Student name Class Lunch choice
2 8/25/2023 8:22:20 Isaac Class 1 Vegan curry
3 8/25/2023 8:36:44 James Clerk Class 3 Vegan curry
4 8/25/2023 8:51:08 Albert Class Greek salad
5 8/25/2023 9:05:32 Richard Class 1 Vegan curry
6 8/25/2023 9:19:56 Edward Class 1 Lentil soup
7 8/25/2023 9:34:20 Lisa Class 3 Greek salad
8 8/25/2023 9:48:44 Juan Martín Class 2 Vegan curry

I then have a separate sheet called Lunch where I would like to get results like this:

A B C D                         
1 Lunch choice Class 1 Class 2 Class 3
2 Greek salad 1 1
3 Lentil soup 1
4 Vegan curry 2 1 1

I don't have much experience with the QUERY function.

  • I've tried putting it inside an IF function
  • I've tried copying the Timestamp into another column so that I can format the date out of the Timestamp
  • I've tried comparing that date with the TODAY function but it keeps returning FALSE.
1
  • Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Aug 24, 2023 at 22:45

2 Answers 2

2

To get the count of each type of lunch in D by classes in C where the datetime in A is today, use query(), like this:

=query( 
  'Form Responses'!A1:D, 
  "select D, count(D) 
   where toDate(A) = toDate(now()) 
   group by D 
   pivot C", 
  1 
)

See query().

1

You need to remove the time from the Timestamp in order to only consider the date. You can do it using the INT function.

I don't know what formula you're using, but an example using COUNTIF:

=COUNTIF(INDEX(INT('Form Responses'!A:A)),TODAY())

Or you could use it inside COUNTIFS to add other criterion based on your other conditions, or inside FILTER.

Your Answer

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

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