Google Sheet SUMIF 不求和范围 [英] Google Sheet SUMIF not summing range

问题描述

我试图根据行的值大于 0 的条件对所有行求和.但是，该公式仅计算第一行并忽略范围的其余部分.我在另一个电子表格中遇到了这个问题，但我在一个新电子表格上隔离了这个问题，以便向您展示并提出问题.

答案应该是 B1(5 美元)、B2(15 美元)、B3(30 美元).我按照 Google Docs

---

更新 - 以下是我的预期目的.我有一个电子表格，可以计算进出资金.对于这些交易中的每一个，我只想要这些交易的余额.这仅适用于第一行.(我对其余行的值进行硬编码只是为了显示我的目标.)

解决方案

尝试:

=ARRAYFORMULA(IF(A:A="",,SUMIF(ROW(A:A), "<="&ROW(A:A), A:A)))

---

如果您更喜欢拖动，请执行以下操作:

=SUMIF(INDIRECT("A1:A"&ROW()), ">0")

---

更新:

=ARRAYFORMULA(IF(D2:D=",,SUMIF(ROW(D2:D), "<="&ROW(D2:D), D2:D)-SUMIF(ROW(E2:E), "<="&ROW(E2:E), E2:E)))

I am trying to SUM all rows based on the condition that a row's value is greater than 0. However, the formula is only calculating the first row and ignoring the rest of the range. I encountered this issue on a different spreadsheet but I isolated the issue on a new spreadsheet to show you and ask questions.

The answers should be B1 ($5), B2 ($15), B3 ($30). I followed the formula's usage as explained in the Google Docs https://support.google.com/docs/answer/3093583?hl=en

The formula with respective row number =SUMIF(A1, ">0", A$1:A1)

---

Update - The below is my intended purpose. I have a spreadsheet that calculates money In and Out. For each of these transactions, I simply want the balance as a result of such transactions. This only works for the first row. (I hardcoded the values for the rest of the rows just to show my goal.)

解决方案

try:

=ARRAYFORMULA(IF(A:A="",,SUMIF(ROW(A:A), "<="&ROW(A:A), A:A)))

---

if you prefer dragging do:

=SUMIF(INDIRECT("A1:A"&ROW()), ">0")

---

UPDATE:

=ARRAYFORMULA(IF(D2:D="",,
 SUMIF(ROW(D2:D), "<="&ROW(D2:D), D2:D)-
 SUMIF(ROW(E2:E), "<="&ROW(E2:E), E2:E)))

这篇关于Google Sheet SUMIF 不求和范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！