I want a cell to give me the sums of the cells above, only when another column has values in it as well. For instance, I want B5 to sum up B1:B4, only when C1:C4 also have values, if a C cell doesn't have a value, then I want the corresponding B cell to be counted as a 0 during the sum. I looking up info on SumIf and FILTER but I don't know what to put in the criteria to make it accept any number, and how to report 0 when no number is available. Any help would be appreciated.
我想要一个单元格给出上面单元格的和，只有当另一列也有值的时候。例如，我希望B5将B1:B4相加，只有当C1:C4也有值时，如果C单元格没有值，那么我希望在求和期间将相应的B单元格计数为0。我在SumIf和FILTER上查找信息，但是我不知道在标准中应该添加什么以使它接受任何数字，以及如何在没有数字的情况下报告0。如有任何帮助，我们将不胜感激。
What I tried doing so far: =FILTER(B1:B4;C1:C4=value() and I get an error
到目前为止我所尝试的:=FILTER(B1:B4;C1:C4=value()，我得到了一个错误
2 个解决方案
#1
26
Have you tried using SUMIF like this:
你有没有试过这样使用SUMIF:
=SUMIF(C1:C4, "<>", B1:B4)
?
吗?
It's working for me.
这对我的工作。
#2
2
Try:
试一试:
=SUMIF(C1:C4; "<>"; B1:B4)
or also, as Jerry suggested:
或者，正如杰里所说:
=SUMIF(C1:C4, "<>", B1:B4)
Note that the semi-colon separator is supported in all locales, so it is generally preferred.
注意，在所有地区都支持分号分隔符，所以通常首选它。
#1
26
Have you tried using SUMIF like this:
你有没有试过这样使用SUMIF:
=SUMIF(C1:C4, "<>", B1:B4)
?
吗?
It's working for me.
这对我的工作。
#2
2
Try:
试一试:
=SUMIF(C1:C4; "<>"; B1:B4)
or also, as Jerry suggested:
或者，正如杰里所说:
=SUMIF(C1:C4, "<>", B1:B4)
Note that the semi-colon separator is supported in all locales, so it is generally preferred.
注意，在所有地区都支持分号分隔符，所以通常首选它。