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

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.