CountifS + 多个条件 + 不同计数

问题描述

我正在寻找计算公式:不同的计数 + 多个条件Countifs() 做了但不包括不同的计数...

这是一个例子.

我有一个表格，我想在该表格上计算满足多个条件的不同项目(列项目)的数量，其中 A 列和 B 列:A>2 和 B

行项目 ColA ColB1 QQ 3 42 QQ 3 33 QQ 5 44 TTT 4 45 TTT 2 36 TTT 0 17 XXX 1 28 XXX 5 39 zzz 1 9

Countifs 是这样工作的: COUNTIFS([ColumnA], criteria A, [ColumnB], criteria B)

COUNTIFS([ColumnA], > 2 , [ColumnB],

返回:第 1、2、4、5、8 行 => 计数 = 5

如何添加基于项目列的非重复计数功能?:

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

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

第 8 行在一个独特的项目 XXX

返回计数 = 3

我怎么能数到 3?！

谢谢

您可以下载excel文件@

然后，抓住物品"带有 INDEX 的列:

=INDEX(FILTER(Table,(Table[Column A]>2)*(Table[Column B]

接下来，过滤唯一条目:

=UNIQUE(INDEX(FILTER(FILTER(Table,(Table[Column A]>2)*(Table[Column B]

最后进行计数:

=COUNTA(UNIQUE(INDEX(FILTER(Table,(Table[Column A]>2)*(Table[Column B]

I'm looking for a formula calculating : distinct Count + multiple criteriaCountifs() 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.

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

解决方案

Newer versions of Excel allow for this problem to be solved in a (relatively) more simple way. It certainly is easier to follow and understand, conceptually.

First, filter the table based on multiple criteria (join multiple with the *):

=FILTER(Table,(Table[Column A]>2)*(Table[Column B]<5))

Then, grab the "Item" column with INDEX:

=INDEX(FILTER(Table,(Table[Column A]>2)*(Table[Column B]<5)),,2)

Next, filter for unique entries:

=UNIQUE(INDEX(FILTER(Table,(Table[Column A]>2)*(Table[Column B]<5)),,2))

Finally, perform a count:

=COUNTA(UNIQUE(INDEX(FILTER(Table,(Table[Column A]>2)*(Table[Column B]<5)),,2)))

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