Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Is there a function that allows me to calculate the total value of column B if column A is equal to "some text". I'm making a budget for myself.

Currently, my Google Spreadsheet 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 but how do I do it over several cells and SUM values together? So the if statement would be =if(A1="food",B1,0)

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.