# How to use an IF statement over several cells?

I'm making a budget for myself. Currently, my Google Spreadsheets looks something like this:

``````----A---- -----B----
food         50
alcohol         20
food        120
dance         10
garden         20
food         20
dance         10
``````

I would like to get the total amounts like this:

``````----E---- -----E----
food        190
alcohol         20
dance         20
garden         20
``````

I can use an IF statement on a single cell so the IF statement would be:

``````=if(A1="food",B1,0)
``````

but how do I do it over several cells and SUM values together?

Is there a function that allows me to calculate the total value of column B if column A is equal to "some text"?

This is best done like this:

## Formula

1. `=QUERY(DATA!A:B;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A")`
2. ```=QUERY(DATA!A:B;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A Label SUM(B)'Units'")```

## Explained

If you include headers, then the `QUERY` function will add them as such and alter them accordingly, ie. `SUM title` or `COUNT title`....etc. You can also added a custom label to the calculated columns, see 2nd example (3rd sheet). The rest of the function will perform as standard SQL.

## Example

I've created an example file for you: sum over several cells

## References

• I ended using the SUMIF statement =SUMIF(range to be matched, criteria,sum range) =SUMIF(\$A\$1:\$A\$7,E1,\$B\$1:\$B\$7) Thank you for your answer. – Luen Sep 12 '13 at 2:17
• @Luen that's also possible, but far more laborious. If you're interested, I prepared an example overview with the usage of `SUMIF` in combination with `UNIQUE`. – Jacob Jan Tuinstra Sep 13 '13 at 7:13

You ask for an IF statement but have accepted an A that does not require that so may wish to consider a pivot table. The coding for this is "behind the scenes", so no formula or function is necessary: The row sorting is a 'bonus' and usually convenient but it does mean more work would be required if to order the output exactly as you show, for example with:

``````=sort(E2:F5,2,false)
``````

(if the pivot table is moved into E2:F6 as shown).

For as simple an example as you show further versatility would be of little use but with a larger data set other features of a pivot table might be appreciated and could involve no more than an additional few clicks. For example how many of each `A` value (eg in the case of `food` here `3`), or the average of `B` by each `A` value (eg in the case of `dance` here `10`).