3

I have a spreadsheet that collects dozens of payments made daily through different means. For example, utilities bills, credit cards bills, personnel fees, expenses, etc. They are made in cash, by homebanking, by ATM, by check, and by courier. I managed to filter them daily with for example:

=SUM(FILTER(C3:C;D3:D="Homebanking";F3:F="november 10 2013"))

where it will show how much money was paid through homebanking on 11/10/2013.

Column C holds the values in $
Column D holds the means of payments values
Column F holds the dates (converted to mmmm dd yyyy because I couldn't get the formula to read 11/10/2013)

However, this method is taking too long because I need to add this formula daily for each item and each means of payment. I have been looking for a way automatically to generate a daily filter that displays how much has been paid on a daily basis for each item.

Is there a better way to sort through many dates and sum all values by day, item and means of payment?

EDIT: SS with dummy data

It would be easier to work with filters, just as I would in Excel, but I'm thinking about filtering by date and then publish it as a web app, or at least as a web embedded chart.

  • 1
    Why don't you share some dummy info with us. The QUERY formula will be best equipped to do the job. – Jacob Jan Tuinstra Nov 15 '13 at 19:15
  • @JacobJanTuinstra: Thanks for your reply, I've just added a SS with dummy data. – Gabriel Crivelli Nov 15 '13 at 19:46
2

The following formula would yield the most elegant presentation.

Formula

=QUERY(DATA!A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B")

Screenshot

enter image description here

This is a good alternative, but formula wise a bit different.

Formula

=QUERY(DATA!A:C, "SELECT A, B, SUM(C) WHERE A IS NOT NULL GROUP BY A, B   
   LABEL SUM(C) 'Costs'")

Screenshot

enter image description here

Example

I've added the results into your dummy sheet.

  • Just awesome! Both alternatives serve same purpose, and data is displayed elegantly! Thanks a lot! – Gabriel Crivelli Nov 15 '13 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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