当该行中的多个其他单元格的总数等于5时，仅在列中添加单元格 [英] Only add cells in column when the total of multiple other cells in that row equals 5

问题描述

所以我将资料汇入Google试算表的试算表。我无法编辑该电子表格。

So I am getting data fed into a spreadsheet on Google Sheets. I cannot edit that spreadsheet.

我在文档中附加了一张表格，其中列出了该表格中的列。表格收到信息，如：

I have an additional sheet in the document that totals up the columns in this sheet. The sheet received info such as:

  A  B  C  D        This info is not actually in the sheet
|------------|        \/    \/
| 4  1  0  0 |      <-- Valid
| 3  1  1  0 |      <-- Valid
| 3  1  0  2 |      <-- Invalid
| 3  0  1  1 |      <-- Valid
|------------|

每个单独行的总和应为5，但是我目前还没有一种验证方式形式本身。所以我需要忽略或删除数据，如果它是违规的。

The total of each individual row should be 5, but I do not currently have a way of validating this in the form itself. So I need to disregard or remove the data if it is in violation.

所以基本上，我想添加列A的所有值，但只有行其中这4个细胞的总数恰好为5个;

So basically, I want to add all the values of column A together, but only of the rows in which the total of these 4 cells are exactly 5; No more, no less.

最近/最接近的是使用SUMIF，但是我需要一个单元格，表示该行是否总计达5个，如前所述，我无法编辑该特定表单中的单元格。

The furthest/closest I have gotten was using SUMIF, but the I need a single cell indicating whether or not the row totals up to 5, and as I said before, I cannot edit the cells in that specific sheet.

推荐答案

尝试这样：

=SUMPRODUCT((($A$1:$A$4+$B$1:$B$4+$C$1:$C$4+$D$1:$D$4)=5)*$A$1:$A$4)

这篇关于当该行中的多个其他单元格的总数等于5时，仅在列中添加单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！