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

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.

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

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

see:

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