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

问题描述

我是数组公式的新手，并注意到虽然 SUBTOTAL 包含许多函数，但它没有 COUNTIF(只有 COUNT 和 <代码>COUNTA).

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

我有一个矩阵，它的一个小子集看起来像:

A B C D E48 53 46 64 6648 66 8940 38 42 49 4437 33 35 39 41

感谢

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

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

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

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

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.

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))),""))

Remember, I am no longer looking for the average: I want to filter rows for whether they have full data, and if they do, I want to count rows with at least 1 entry > 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))))))

Data

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