Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the following data in a google spread sheet.

ColumnA         ColumnB
=======================
Bus             205
Train           10
Train           10
Fruit           10

I want to sum all ColumnB where ColumnA is equal to either Bus or Train.

I know that I can sum all content of B based on single value of A like below

=(SUMIF(A:A, "Bus", B:B))

But I want to sum if column A contains any of the values that I specify. How can I do that?

share|improve this question
up vote 2 down vote accepted

Short answer

=ArrayFormula(sum(sumif(A:A,{"Bus";"Train"},B:B)))

Explanation

  • A:A is the column containing the categories.
  • {"Bus";"Train"} is a 1x2 array. It could be replaced by a range 1xn arrange containing the categories to be included in the sum.
  • B:B is the column containing the values to be summed.

  • sumif returns of the sum of values corresponding to each specified category

  • sum returns the sum of the returned array by sumif
  • ArrayFormula :

Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.

Reference

share|improve this answer

This should work:

=sumproduct((A:A="Bus")+(A:A="Train"),B:B)
share|improve this answer

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.