Excel forumla：CountifS +多个条件+不同的计数 [英] Excel forumla : CountifS + multiple criteria + distinct count

问题描述

我正在寻找一个公式计算：不同的Count +多个条件
Countifs（）做，但不包括不同的计数...

这里有一个例子。

我有一个表，我想计算满足多个条件的不同项目（列项）数量A和B：A> 2和B< 5。

输入图片这里的描述

 行项目ColA ColB 
 1 QQQ 3 4 
 2 QQQ 3 3 
 3 QQQ 5 4 
 4 TTT 4 4 
 5 TTT 2 3 
 6 TTT 0 1 
 7 XXX 1 2 
 8 XXX 5 3 
 9 zzz 1 9 
  

Countifs的工作方式如下：COUNTIFS（[ColumnA]，criteria A， ColumnB]，标准B）

COUNTIFS（[ColumnA]，> 2，[ColumnB]，< 5）

返回：行1,2,4,5,8 => Count = 5

如何根据项目列添加一个不同的计数功能：

行1,2在一个唯一的项目上QQQ

第8行是唯一的项目XXX

返回Count = 3

如何计数3？

谢谢

您可以下载excel文件@ Excel文件

解决方案

丑陋的公式，但它的作品。

  = SUM（（（FREQUENCY（IF（C2：C10> 2,1,0））* IF（D2：D10 < 0）*（COUNTIF（B2：B10， > 中&安培; B2：B10）+1），ROW（B2：B10）-ROW（B2）））*（ROW（B2：B11）-ROW（B2） ）> 0）* 1）
  

我将从标准IFS开始：

（B2：D10< 5,1,0）


/ code>

为满足两个条件的行提供1和0的数组。对于您的示例，ARRAY = {1; 1; 1; 1; 0; 0; 0; 1; 0}

其中B2：B10是Item列， COUNTYF（B2：B10，>& B2：B10）

返回{6; 6; 6; 3; 3; 3; 1; 1; 0}其中数字等于项目数价值B2：B10字母小于测试项目值。

• QQQ转到6 [3TTT，2XXX 1zzz]


• TTT转到3 [2XXX，1zzz]


• XXX转到1 [1zzz ]


• zzz转到0 [0小于zzz]

需要添加1到此数组，以确保没有0值：
{7; 7; 7; 4; 4; 4; 2; 2; 1}

所以当乘以标准和countif语句：

 （IF（C2：C10> 2,1,0 ）* IF（D2：D10 <5,1,0）*（COUNTIF（B2：B10，>& B2：B10）+1）
  

您可以得到ARRAY = {7; 7; 7; 4; 0; 0; 0; 2; 0}

  FREQUENCY（ARRAY，ROW（B2：B10）-ROW（B2））
  / pre> 
 
 
 ROW（B2：B10）-ROW（B2）将频率仓设置为{0; 1; 2; 3; 4; 5; 6; 7; 8}。因此，频率公式的输出是{4; 0; 1; 0; 1; 0; 0; 3; 0; 0}，其中最后0是大于8的所有值。
 
 
 
（（ROW（B2：B11）-ROW（B2）> 0）* 1）等于{0; 1; 1; 1; 1; 1; 1; 1; 1; 1}。将ARRAY乘以此将在开始时消除0计数：ARRAY = {0; 0; 1; 0; 1; 0; 0; 3; 0; 0}。 [注意：B11是最低项目列单元格+ 1，因为从超过8的值的频率公式中添加的数组值] 
 
 
 
（ARRAY）> 0）* 1 = {0; 0; 1; 0; 1; 0; 0; 1; 0; 0} 
 
 
 
 SUM this = 3。
 
 
 
 ctrl + shift + enter，因为它是一个数组公式。
 
 
 
 cmd + shift + enter for mac。
 
I'm looking for a formula calculating : distinct Count + multiple criteria
Countifs() does it but do not includes distinct count...

Here is an example.

I have a table on which I want to count the number of distinct items (column item) satisfying multiple conditions one column A and B : A>2 and B<5.

enter image description here
Line  Item  ColA  ColB
1     QQQ    3     4
2     QQQ    3     3
3     QQQ    5     4
4     TTT    4     4
5     TTT    2     3
6     TTT    0     1
7     XXX    1     2
8     XXX    5     3
9     zzz    1     9
Countifs works this way : COUNTIFS([ColumnA], criteria A,   [ColumnB], criteria B)

COUNTIFS([ColumnA], > 2 ,   [ColumnB], < 5)

Returns : lines 1,2,4,5,8 => Count = 5

How can I add a distinct count function based on the Item Column ? :

lines 1,2 are on a unique item QQQ

lines 4,5 are on a unique item TTT

Line 8 is on a unique item XXX

Returns Count = 3

How can I count 3 ?!

Thanks

You can download the excel file @ Excel file
解决方案
Ugly formula, but it works.
=SUM(((FREQUENCY(IF(C2:C10>2,1,0)*IF(D2:D10<5,1,0)*(COUNTIF(B2:B10,">"&B2:B10)+1),ROW(B2:B10)-ROW(B2)))*(ROW(B2:B11)-ROW(B2))>0)*1)
I'll start with the criteria IFS:
IF(C2:C10>2,1,0)*IF(D2:D10<5,1,0)
Gives an array of 1s and 0s for the rows that satisfy both criteria. ARRAY = {1;1;1;1;0;0;0;1;0} for your example.

Where B2:B10 is the Item column, the countif formula:
COUNTIF(B2:B10,">"&B2:B10)
returns {6;6;6;3;3;3;1;1;0} where the number equals the number of item values in B2:B10 alphabetically less than the tested item value.


QQQ goes to 6 [3"TTT", 2"XXX", 1"zzz"]
TTT goes to 3 [2"XXX", 1"zzz"]
XXX goes to 1 [1"zzz"]
zzz goes to 0 [0 less than "zzz"]


Need to add 1 to this array to make sure there are no 0 values:
{7;7;7;4;4;4;2;2;1}.

So when multiplying the criteria, and the countif statement:
(IF(C2:C10>2,1,0)*IF(D2:D10<5,1,0)*(COUNTIF(B2:B10,">"&B2:B10)+1)
You get ARRAY = {7;7;7;4;0;0;0;2;0}.
FREQUENCY(ARRAY,ROW(B2:B10)-ROW(B2))
ROW(B2:B10)-ROW(B2) sets the frequency bins to {0;1;2;3;4;5;6;7;8}. So the output of the frequency formula is {4;0;1;0;1;0;0;3;0;0} where the last 0 is for all values greater than 8. 

((ROW(B2:B11)-ROW(B2)>0)*1) equals {0;1;1;1;1;1;1;1;1;1}. Multiplying ARRAY by this removes the 0 count at the start: ARRAY = {0;0;1;0;1;0;0;3;0;0}. [NOTE: B11 is lowest item column cell+1 because of the added array value from the frequency formula for values over 8]

(ARRAY)>0)*1 = {0;0;1;0;1;0;0;1;0;0}

SUM this = 3.

ctrl + shift + enter, because it's an array formula.

cmd + shift + enter for mac.

                        
这篇关于Excel forumla：CountifS +多个条件+不同的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！