CountifS + multiple criteria + distinct count

Question

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.

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

Answer 1

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