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
Customer	Contract type	Customer	Uniqe contract count

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]B[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]B[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Unique contract count​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]I7[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Customer	Contract	Customer	Most freq. contract
A
B
A
A
C
A
B
B

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Return most freq. contract​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]I10[/TH]
[TD="align: left"]{=INDEX($E$10:$E$17,MODE(MATCH($E$10:$E$17,$E$10:$E$17,0)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[...]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Customer[/TD]
[TD]Contract[/TD]
[TD][/TD]
[TD][/TD]
[TD]CUS[/TD]
[TD][/TD]
[TD]CONTRACT(S)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

1​

[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

2​

[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

1​

[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

1​

[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

1​

[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

2​

[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

1​

[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

1​

[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

4​

[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

4​

[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

2​

[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

#N/A​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

3​

[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]

5​

[/TD]
[TD]

#N/A​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]

3​

[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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