1

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.

2

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

  • 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 '14 at 13:15
  • Ok I found out I have to adjust the SUM(C) accordingly! – tzippy Jan 10 '14 at 13:20
  • How Do I sort by Amount? GROUP BY B SORT BY E for example (does not work)? – tzippy Jan 10 '14 at 13:29
  • @tzippy See revised answer. Good luck (Servus). – Jacob Jan Tuinstra Jan 10 '14 at 14:54
-1

=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

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.