In Google Spreadsheets, how do you iterate over a cell range and use the index counter?

Question

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.

Answer 1

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

Answer 2

=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