将Countif添加到Excel中的数组公式(小计) [英] Add Countif to Array Formula (Subtotal) in Excel

问题描述

我是数组公式的新手，并且注意到，虽然SUBTOTAL包含许多函数，但它没有COUNTIF功能(仅COUNT和COUNTA).

I am new to array formulae and have noticed that while SUBTOTAL includes many functions, it does not feature COUNTIF (only COUNT and COUNTA).

我试图弄清楚如何将类似COUNTIF的功能集成到我的数组公式中.

I'm trying to figure out how I can integrate a COUNTIF-like feature to my array formula.

我有一个矩阵，其中的一小部分看起来像:

I have a matrix, a small subset of which looks like:

A   B   C   D   E
48  53  46  64  66
48      66  89
40  38  42  49  44

37  33  35  39  41

感谢 @Tom Shape 的帮助

Thanks to the help of @Tom Shape in this post, I (he) was able to average the sum of each row in the matrix provided it had complete data (so rows 2 and 4 in the example above would not be included).

现在，我想计算包含完整数据的行数(因此，将忽略第2行和第4行)，其中包括至少一个超过给定阈值的值(例如45).

Now I would like to count the number of rows with complete data (so rows 2 and 4 would be ignored) which include at least one value above a given threshold (say 45).

在当前示例中，结果将为2，因为行1的5/5值> 45，行3的1值>45.行5的值< 45和行2和3分别具有部分或全部丢失的数据.

In the current example, the result would be 2, since row 1 has 5/5 values > 45, and row 3 has 1 value > 45. Row 5 has values < 45 and rows 2 and 3 have partially or fully missing data, respectively.

我最近发现了SUMPRODUCT函数，并认为SUMPRODUCT(--(A1:E1 >= 45可能有用，但是我不确定如何将其集成到

I have recently discovered the SUMPRODUCT function and think that perhaps SUMPRODUCT(--(A1:E1 >= 45 could be useful but I'm not sure how to integrate it within Tom Sharpe's elegant code, e.g.,

=AVERAGE(IF(SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1)))=COLUMNS(A1:E1),SUBTOTAL(9,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1))),""))

请记住，我不再寻找平均值:我想过滤行中是否有完整的数据，如果有，我要对至少有1个条目> 45的行进行计数.

推荐答案

尝试以下方法.输入作为数组公式.

Try the following. Enter as array formula.

=COUNT(IF(SUBTOTAL(4,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1)))>45,IF(SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1)))=COLUMNS(A1:E1),SUBTOTAL(9,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1))))))

数据

这篇关于将Countif添加到Excel中的数组公式(小计)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！