excel - 根据单个列计算行数，以及多列的总和

Question

在 excel 中，我需要根据单个列中的值的标准以及一组多列的总和来计算行数。

例如，通过附加的数据图像，我希望能够计算“有效”的行数(= 当 A 列 = 1 时)，超过 17 岁(= B 列 = >17)，并且当列 C 到 G 的总和为零时。

我目前正在使用 countifs函数，C 到 G 列的单独标准为零。但是，对于我的实际数据，列数比 C 到 G 多得多，因此公式变得太大，编写它们需要很长时间并且很容易出错。

我试过使用 sumproduct但这与长公式有同样的问题。

我尝试过的公式；

=COUNTIFS($A:$A, 1, $B:$B, ">17", $C:$C, 0, $D:$D,0, $E:$E,0,  $F:$F,0, $G:$G,0)

=SUMPRODUCT(($A:$A=1)*($B:$B>17)*($C:$C=0)*($D:$D=0)*($E:$E=0)*($F:$F=0)*($G:$G=0))

我知道我可以使用 If 创建一个新列C-G 列之和的函数 first (=IF(SUM(C2:G2)=0, 1, 0)) ，然后将此作为条件(新列 = 1)包含在 countifs 中，但我想避免使用这些数据创建新列。

我想做 =COUNTIFS($A:$A, 1, $B:$B, ">17", C:G, SUM(C:G)=0) , 但总和在 countifs不可能。

任何想法将不胜感激？

数据图片:



样本数据:

| Valid | Age | a | b | c | d | e |
|-------|-----|---|---|---|---|---|
| 1     | 18  | 0 | 0 | 0 | 0 | 0 |
| 0     | 8   | 1 | 0 | 0 | 0 | 1 |
| 0     | 48  | 0 | 1 | 0 | 0 | 1 |
| 1     | 22  | 0 | 0 | 0 | 0 | 0 |

Answer 1

你说你对你的COUNTIFS不满意公式，但这是仍然使用 COUNTIFS 的有效方法(比 VBA 更短/更简单)并保持准确性 .

The formula with your example would be: =COUNTIFS(A:A,1,B:B,">=18",C:C,,D:D,,E:E,,F:F,,G:G,)

This is likely similar what you were using, but note:

• If the criteria is "=0" then no criteria need to be specified between the commas.
• If the function applies to the entire column, there is no need to specify row numbers.
  _{In fact, even if there's other values above/below your data (like headings), they'll be ignored
  (unless they meet all the criteria in the formula, which would be unlikely.)}

想避免错误？让Excel为你写公式!

如果您担心在将公式调整为实际数据时出错，请让 Excel 通过三个步骤为您完成工作。 (首先 保存 你的工作簿。)

选择每一列 (或每列中的一个单元格)要包含在公式中。 (要选择多个区域，请单击第一个区域，然后在单击其余区域时按住 Ctrl。)

复制下面的行通过突出显示它并按 Ctrl+C:

?"=COUNTIFS(A:A,1,B:B,"">=18""";:For Each c In Selection.Columns:?","&Columns(c.Column).Address&",";:Next c:?")"

点击 Alt+F11 然后 Ctrl+GV 然后 Enter (这会立即运行步骤 2 中的 VBA 代码。)

公式将出现在下一行 .只需复制/粘贴到您想要的位置!

示例输出: =COUNTIFS(A:A,1,B:B,">=18",$C:$C,,$F:$F,,$G:$G,,$P:$P,,$T:$T,,$AJ:$AJ,,$AC:$AC,)
编辑:

我不确定你为什么想要/需要使用 INDIRECT但只要您保持相同的语法就可以了。一个简化的例子:

我在 A 列和 B 列中有值。我想计算 A=1 和 B=1 的行。我的 COUNTIFS公式(在 D2 中)将是: =COUNTIFS(A:A,1,B:B,1)


使用原因 INDIRECT可能是，例如，我不总是希望它使用 B 列——我希望能够在不同的单元格中指定要使用的列，也许是 D5 .

在 D5我输入文字: B:B .然后我把公式改成 D2 , 替换 B:B与 INDIRECT(D5) .



由于 E2包含文本 B:B ，公式还是间接引用 B:B ，公式的结果不变。如果我在 E2 中指定不同的列范围，公式将查看新范围。

Important Note:

With SUMIFS, COUNTIFS and AVERAGEIFS, all ranges specified must have the same number of rows and columns as the criteria_range1 argument.

In this formula, that means they must be entire columns.

如果您只是想引用不同工作表上的列，只要使用正确的语法就可以，例如: =COUNTIFS(A:A,1,Sheet1!D:D,1)引用 Sheet1 上的 D 列.

如果工作表的名称作为文本包含在单元格 ( D5 ) 中，但您希望列 ( D:D ) 仍然是硬编码的，那么您的 INDIRECT公式的一部分是: INDIRECT(D5&"!D:D")


如果指定的工作表名称中有空格，那么您需要用 ' 将名称括起来使徒，在正确的地方:

=COUNTIFS(A:A,1,INDIRECT("'" & D5&"'!D:D"),1)

...这是在命名“任何东西”时应避免使用空格和其他非字母数字字符的众多原因之一。