0

I have a list of transactions for a month, all include a date (Column B - dd/mm/yyyy) and a £in figure (Column F). I want to create a table that shows the total taken on each day of the month so for example on the April sheet, calculate the sum of all sales on the 1st, 2nd, 3rd

=SUM(QUERY($A$3:$F, "select F where 'TEXT(B,dd)' = 1",0))

=SUM(QUERY($A$3:$F, "select F where 'TEXT(B,dd)' = 2",0))

=SUM(QUERY($A$3:$F, "select F where 'TEXT(B,dd)' = 3",0))

This completes the query successfully but returns no results, despite there being transactions on these days.

Thanks

It would also be ideal if there was a way to input the formula so it can be filled into each row upto 31, without changing the date on each row

sample sheet with relevant data

https://docs.google.com/spreadsheets/d/1nkwh1F3_3_bFdakkijjjRcKMUHJUOJ22QJKOWjzycnM/edit?usp=sharing

Improve this question
3
  • Welcome. Please share the link to a test sheet Commented Jan 26, 2020 at 15:55
  • docs.google.com/spreadsheets/d/… Commented Jan 26, 2020 at 19:06
  • Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. Commented Jan 26, 2020 at 19:15

2 Answers 2

Reset to default
1

To extract the day of the month by using QUERY, use day()

Example:

=QUERY($A3:$F,"select F where day(B) = 1")

Reference

Improve this answer
0

What you need is a query formula:

=query(A1:B, "select A, sum(B) where A is not null group by A order by A asc label sum(B) 'Takings'",1)

You can then format your cells to show just the day of the date or whatever you like by going to:
Format >> Number >> More formats >> More date and time formats

Functions used:

Improve this answer
2
  • Thanks, This is very close to what I need, but I want to see the sum for every day, even if there are no transactions Commented Jan 26, 2020 at 19:55
  • "It would also be ideal if there was a way to input the formula so it can be filled into each row up to 31, without changing the date on each row". Have you solved your issue. Was day(B) = 1 enough? Commented Jan 28, 2020 at 16:59

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.