4

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?

3 Answers 3

4

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

4

This should work:

=sumproduct((A:A="Bus")+(A:A="Train"),B:B)
0

SUMIFS Function

While SUMIF returns the sum of a range based on a single condition, the SUMIFS function returns the sum of a range based on multiple criteria.

Syntax

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Formula

=SUMIFS(B:B, A:A, "Bus", A:A, "Train")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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