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 table in which I have a list of amounts, each belonging to one of 15 categories

    A         B
1    cat  |  amount
    _____|________
2    1    |   12
3    1    |    4
4    4    |   18,90
5    15   |   19,79
6    4    |   23
7    4    |   12
8    15   |   19
9    9    |    1,90
10   8    |   18

I want to list each category with the sum of all amounts belonging to this category. For each category, for example 1 I could do =SUM(IF(A2=1;B2;0) + IF(A3=1;B3;0) + IF(A4=1;B4;0)) I was hoping to find something like =FOR((A2:A10);SUM(IF(Ai=1;Bi;0))) that iterates over all rows A2 to A10 and sums up all amounts where category is 1.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

That's best done via the QUERY formula.

Formula

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

If you want to sort by amount, then use the following formula:

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

I've added this formula to the example file.

Example

I've created an example file for you: sum by query

share|improve this answer
    
Well thank you! One Question though: For the first argument DATA!B:C I would like to select data from two columns that are not next to each other like in my sample. How would I do that? –  tzippy Jan 10 at 13:15
    
Ok I found out I have to adjust the SUM(C) accordingly! –  tzippy Jan 10 at 13:20
    
How Do I sort by Amount? GROUP BY B SORT BY E for example (does not work)? –  tzippy Jan 10 at 13:29
    
@tzippy See revised answer. Good luck (Servus). –  Jacob Jan Tuinstra Jan 10 at 14:54

=sumif(A2:A,1,B2:B)

see:

http://anandexcels.wordpress.com/tag/google-docs-sumif-function/

there are also database functions, e.g. dsum() and SQL style QUERY() functions

share|improve this answer

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.