I have table data in Google Spreadsheet something like this:
Date|Diet
4-Jan-2020|Coffee
4-Jan-2020|Snacks
4-Jan-2020|xyz
4-Jan-2020|Coffee
5-Jan-2020|Snacks
5-Jan-2020|abc
6-Jan-2020|Coffee
6-Jan-2020|Snacks
This table is a list of food items I had on a daily basis. I would like to get the number of times I had coffee on a daily basis. So I would like to get the output like this:
Date | No of times I had Coffee
4-Jan-2020| 2
5-Jan-2020| 0
6-Jan-2020| 1
I used this query to get the output.
=query(A1:B1425,"select A, COUNT(B) where B='Coffee' group by A")
With this query, I get the below output. Do note that I don't get those days when I didn't have coffee
4-Jan-2020| 2
6-Jan-2020| 1
So count for 5-Jan-2020 is missing because there is no string "Coffee" for that day.
How do I get the desired output including the count 0? Thank you.
Aucun commentaire:
Enregistrer un commentaire