Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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 been looking for a way to automatically 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?

Thanks a lot in advance.

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 webapp, or at least as a web embedded chart.

share|improve this question
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

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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