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).