Google Sheet SUMIF not summing range

2020-04-26 23:38发布

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)

enter image description here


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

enter image description here

1条回答
手持菜刀,她持情操
2楼-- · 2020-04-27 00:42

try:

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

0


if you prefer dragging do:

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

0


UPDATE:

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

0

查看更多
登录 后发表回答