Query form data by date to return counts by item and category

Question

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.

Answer 1

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().

Answer 2

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.