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

3

2 Answers 2

1

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

Example:

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

Reference

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:

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
    – Mike
    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? Jan 28, 2020 at 16:59

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.