Reputation: 115016
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
and 16
are the results of the formulas):
X 39 -> 10 + 7 + 22
Y 16 -> 3 + 4 + 9
Upvotes: 7
Views: 14914
Reputation: 6032
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 functionality 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.
Upvotes: 0
Reputation: 834
use SUMIF(range, criteria, sum_range)
: (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)
Upvotes: 6
Reputation: 11
What about
=query(A:B, "select A, sum(B) group by A order by sum(B) desc")
Upvotes: 1
Reputation: 16840
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
Upvotes: 2
Reputation: 9423
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)"
Upvotes: 13
Reputation: 2486
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.)
Upvotes: 1