How do I count unique text occurences based on a single criteria where some of the cells are #N/A

[jacquesB]

Hi all

I am trying to count unique text occurences for some customers. As my data sheet includes some #N/A in the cells of the customers (and contract types), I need a formula that takes into account. The current formula does not seem to care if the contract type is #N/A, but I cannot fix it when the #N/A is in the customer column.

Is there any genius in this forum who knows how to solve it?

	D	E	F	G	H	I
6	Customer	Contract type			Customer	Uniqe contract count
7	1	A			1	2
8	2	B			2
9	1	A			3
10	1				4
11	1	C
12	2	A
13	3	B
14	3	B
15	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Unique contract count​

Array Formulas Cell Formula I7 {=SUM(--(FREQUENCY(IF(ISTEXT($E$7:$E$15),IF($E$7:$E$15<>"",IF($D$7:$D$15=H7,MATCH($E$7:$E$15,$E$7:$E$15,0)))),ROW(E7:E15)-ROW(E7)+1)>0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Aladin Akyurek]

In I2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(ISNA($D$2:$D$15),IF(ISNA($E$2:$E$15),
    IF(1-($E$2:$E$15=""),MATCH($E$2:$E$15,$E$2:$E$15,0)))),
    ROW($E$2:$E$15)-ROW($E$2)+1),1))

 

[jacquesB]

You sir, is a genius!

I hope that it is not too much to ask, but I sort of just came across the "opposite" problem as well, and maybe you can see a solution to it? No matter what thank you very much!

I want to return the most frequent text, but now it returns an error when the #N/A is in the contract column. What seems to be the solution? I still need it to be based on the criteria of the customer. I hope that my question makes sense

	D	E	F	G	H	I
9	Customer	Contract			Customer	Most freq. contract
10	1	A			1	A
11	2	B			2
12	1	A			3
13	1	A			4
14	4	C
15	2	A
16	3	B
17	3	B

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Return most freq. contract​

Array Formulas Cell Formula I10 {=INDEX($E$10:$E$17,MODE(MATCH($E$10:$E$17,$E$10:$E$17,0)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Aladin Akyurek]

You sir, is a genius!

I hope that it is not too much to ask, but I sort of just came across the "opposite" problem as well, and maybe you can see a solution to it? No matter what thank you very much!

I want to return the most frequent text, but now it returns an error when the #N/A is in the contract column. What seems to be the solution? I still need it to be based on the criteria of the customer. I hope that my question makes sense

[...]

Row\Col	D​	E​	F​	G​	H​	I​	J​	K​	L​
9​	Customer	Contract			CUS		CONTRACT(S)
10​	1​	A			1​	3​	A	B
11​	2​	B			2​	1​	A	B	C
12​	1​	A			3​	2​	B
13​	1​	A			4​	1​	C	D
14​	1​	B			5​
15​	2​	C
16​	1​	B
17​	1​	B
18​	4​	D
19​	4​	C
20​	2​	A
21​	#N/A​
22​	3​	B
23​	5​	#N/A​
24​	3​	B

<tbody>
</tbody>

D10:D24 >> Customer

E10:E24 >> Contract

Augment the above set of definitions with Ivec which must be defined via Formulas | Name Manager as referring to:

=ROW(Contract)-ROW(INDEX(Contract,1,1))+1

In E10 control+shift+enter, not just enter, and copy down:

=IFERROR(1/(1/MAX(FREQUENCY(IF(1-ISNA(Customer),
   IF(Customer=$H10,IF(1-(Contract=""),MATCH(Contract,Contract,0)))),Ivec))),"")

In J10 control+shift+enter, copy across as far as needed, and down:

=IFERROR(IF($I10="","",INDEX(Contract,SMALL(IF(FREQUENCY(IF(1-ISNA(Customer),
    IF(Customer=$H10,IF(1-(Contract=""),MATCH(Contract,Contract,0)))),Ivec)=$I10,Ivec),
    COLUMNS($J10:J10)))),"")