Formula - Digit Frequency

[ststern45]

Hi everyone,

Need help with a formula.

Cell A1 = 1
Cell B1 = 3
Cell C1 = 7
Cell D1 = 1
Cell E1 = 4
Cell F1 = 5

Cell A2 = 1
Cell B2 = 2
Cell C2 = 3
Cell D2 = 7
Cell E2 = 4
Cell F2 = 1

I would like to count how many digits from cell range A2 through F2 appear in cell range A1 through F1.

1>Remove duplicates from cell range A1 through F1 = 1-3-7-4-5
2>Remove duplicates from cell range A2 through F2 = 1-2-3-7-4
3>Result = 4
digits 1-3-7-4 appear in both cell ranges.

Thanks in advance!!

 

[Besserwisser]

Re: Formula Help - Digit Frequency

Hi,

=SUM(--(FREQUENCY(A2:F2,A1:F1)>0))





Christian

 

[Besserwisser]

Re: Formula Help - Digit Frequency

Hi,

it's more complex than I thougt:

=SUM(MMULT(IFERROR(--((A2:F2)/TRANSPOSE(FREQUENCY((A2:F2),A2:F2)>0)=TRANSPOSE((A1:F1)/TRANSPOSE(FREQUENCY((A1:F1),A1:F1)>0))),0),TRANSPOSE(COLUMN(A1:G1)^0)))


CTRL SHIFT ENTER





Christian

 

[Besserwisser]

Hi,

much more elegant is:

=COUNT(MATCH(TRANSPOSE(A2:F2)/(FREQUENCY(A2:F2,A2:F2)>0),A1:F1,0))






Christian

 

[ststern45]

Thanks for the formula. When I get home later I'll give this a go.
Thanks so much!!