1

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

1

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
  • 1
    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
1

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:

WA49362 example

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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