首页 文章

CountifS多个标准不同计数

提问于
浏览
4

我正在寻找一个公式计算:distinct计数多个标准Countifs()做它但不包括不同的计数...

这是一个例子 .

我有一个表格,我想要计算满足多个条件的不同项目(列项目)的数量,一列A和B:A> 2和B <5 .

Image description here

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以这种方式工作:COUNTIFS([ColumnA],标准A,[ColumnB],标准B)

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

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

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

第1,2行是一个独特的QQQ项目

第4,5行是一个独特的TTT项目

第8行位于唯一的项目XXX上

返回Count = 3

我怎么算3?

谢谢

你可以下载excel文件@ Excel file

2 回答

  • 4

    你可以试试这个:

    =SUMPRODUCT(1/COUNTIF(B2:B10,B2:B10))

    信用到期,但是......我发现它在这里:

    https://exceljet.net/formula/count-unique-values-in-a-range-with-countif

  • 0

    丑陋的公式,但它的工作原理 .

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

    我将从标准IFS开始:

    IF(C2:C10>2,1,0)*IF(D2:D10<5,1,0)
    

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

    其中B2:B10是Item列,countif公式:

    COUNTIF(B2:B10,">"&B2:B10)
    

    返回{6; 6; 6; 3; 3; 3; 1; 1; 0},其中数字等于B2中项目值的数量:B10按字母顺序小于测试项目值 .

    • QQQ进入6 [3 "TTT",2 "XXX",1 "zzz"]

    • TTT转到3 [2 "XXX",1 "zzz"]

    • XXX进入1 [1 "zzz"]

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

    行(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为mac输入 .

相关问题