What would be the spreadsheet function to sum all values in one column based on criteria from another column (I'm using google spreadsheet)

Question

I want a spreadsheet function that will produce a sum of all values in column B for when column A is equal to 'X' and when it is equal to 'Y'

A     B

X    10
Y     3
X     7
X    22
Y     4
Y     9

The output should look like the following (where 39 & 16 are the results of the formulas):

X    39           -> 10 + 7 + 22
Y    16           -> 3 + 4 + 9

Answer 1

One quick and dirty solution is to make two new columns. For each row x, Cx should be something like =Ax=='X'?Bx:0. Do the same for column D, but checking Ax=='Y'. Then sum C and D.

(Not sure if that matches Google Spreadsheet syntax exactly.)

Answer 2

Something like this

X   10
Y   3
X   7
X   22
Y   4
Y   9

X   "=SUMIF(A1:A6;A8;B1:B6)"
Y   "=SUMIF(A1:A6;A9;B1:B6)"

Answer 3

You can use SUMPRODUCT to calculate the totals. For the "X" values:

=SUMPRODUCT((A1:A6="X")*(B1:B6))

for the "Y" values:

=SUMPRODUCT((A1:A6="Y")*(B1:B6))

Hope that helps,

Eric Melski

EDIT: Apparently you must use ARRAYFORMULA to use SUMPRODUCT in Google's spreadsheet. See for example http://www.google.com/support/forum/p/Google+Docs/thread?tid=13a3eb824446e891&hl=en

Answer 4

The fact that google docs doesn't support the sumproduct and the -- operator as Excel does is a bit bothering. You could always replicate the functionnality using more columns, but as you responded in one of your comments, this seems impossible.

You could check if a hidden column is rendered in the form. If it is not, this is the way to go.

Answer 5

use SUMIF: (sum values between B2-B8, using value in A2-A8 as criterea, using the specified condition)

=SUMIF(A2:A8,"=X",B2:B8)

=SUMIF(A2:A8,"=Y",B2:B8)