countif, but only using partial cell contents in the reference array

[jaredh]

=SUMPRODUCT(--(A1:A100=MID(A1000,FIND("{",A1000,1)+1,8)))

that assumes that a1:100 would = the mid(results)
however, i need to mid the a1:a100 to match the a1000 mid(result)

i need to match partial cell contents to partial cell contents

I hope that explains it better

jare

 

[Max]

Use a helper column, say col B ..

Put in B1: =MID(A1,FIND("{",A1,1)+1,8)
Copy down to B100

Then put in C1:
=SUMPRODUCT(--(B1:B100=MID(A1000,FIND("{",A1000,1)+1,8)))

 

[Max]

Try (w/o helper col):

=SUMPRODUCT(--(MID(A1:A100,FIND("{",A1:A100,1)+1,8)=MID(A1000,FIND("{",A1000
,1)+1,8)))