Google表格公式用于带条件的累加总和 [英] Google Sheet formula for cumulative sum with condition

问题描述

我有一个具有以下布局的Google表格:

I have a Google Sheet with the following layout:

  Number |  Counted?  |  Cumulative Total
    4    |     Y      |         4
    2    |            |         6
    9    |     Y      |        15
   ...   |    ...     |        ...

累积总计列中的第一个单元格填充有以下公式:

The first cell in the Cumulative Total column is populated with this formula:

=ArrayFormula((SUMIF(ROW(C2:C1000),"<="&ROW(C2:1000),C2:C1000)

但是，这将计算数字"列中的所有行.如何使累计总数"仅对已计数?单元格为 Y 的行进行计数?

However this counts all rows in the 'Number' column. How can I make the Cumulative Total only count rows where the Counted? cell is Y?

推荐答案

在 C2 中尝试并复制下来:

= N(C1) + A2 * (B2 = "Y")

---

更新

似乎不适用于SUMIF S ，但是有一个非常慢的矩阵乘法替代方法:

Doesn't seem to work with SUMIFS, but there is a very slow matrix multiplication alternative:

=ArrayFormula(MMult((Row(2:1000)>=Transpose(Row(2:1000)))*Transpose(A2:A1000*(B2:B1000="Y")), Row(2:1000)^0))

这篇关于Google表格公式用于带条件的累加总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！