Google Sheets – How to Use an IF Statement Over Several Cells

formulasgoogle sheetsgoogle-sheets-query

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"?

Best Answer

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

  1. SQL
  2. QUERY formula
  3. Query Language Reference
  4. Question with same techniques used
Related Topic