什么是电子表格函数，根据另一列的标准（一个列使用谷歌电子表格）对一列中的所有值求和

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列等于'X'时,它将产生B列中所有值的总和,当它等于'Y'时

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

输出应如下所示(其中39和16是公式的结果):

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

6 个解决方案

#1

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

#2

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

使用SUMIF(范围,标准,sum_range):( B2-B8之间的总和值,使用A2-A8中的值作为criterea,使用指定的条件)

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

#3

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

您可以使用SUMPRODUCT来计算总计。对于“X”值:

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

for the "Y" values:

对于“Y”值:

=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

编辑:显然你必须使用ARRAYFORMULA在Google的电子表格中使用SUMPRODUCT。例如,请参阅http://www.google.com/support/forum/p/Google+Docs/thread?tid=13a3eb824446e891&hl=en

#4

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.

一个快速而肮脏的解决方案是制作两个新列。对于每一行x,Cx应该类似于= Ax =='X'?Bx:0。对D列执行相同操作,但检查Ax =='Y'。然后总结C和D.

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

(不确定这是否与Google Spreadsheet语法完全匹配。)

#5

What about

=query(A:B, "select A, sum(B) group by A order by sum(B) desc")

#6

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.

谷歌文档不支持sumproduct和 - 运算符作为Excel的事实是有点麻烦。您总是可以使用更多列来复制functionnality,但是当您在其中一条评论中做出回应时,这似乎是不可能的。

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

您可以检查是否在表单中呈现隐藏列。如果不是,这就是要走的路。

#1