Sumeproduct with AND, LEN, LEFT not working

[1629dill]

I have neen trying to get formal to work and keep having problems. =SUMPRODUCT(AND(LEN(CSPG_Customer_Reference)=5,LEFT(CSPG_Customer_Reference,2)="10")*1)

I have broken it up into parts and they work fine, but when all put together I do not get back what I should.
When I use this I get 8
=SUMPRODUCT(AND(LEN(CSPG_Customer_Reference)=5,LEFT(CSPG_Customer_Reference,2)="10")*1)
and when I use this I get 8.
=SUMPRODUCT((LEFT(CSPG_Customer_Reference,2)="10")*1)
but when I use this I get 0.
=SUMPRODUCT(AND(LEN(CSPG_Customer_Reference)=5,LEFT(CSPG_Customer_Reference,2)="10")*1)

Would welcome any help I can get to make this work.

Thanks


This is what my data looks like.

Customer Reference
0
0
0
0
0
0
0
0
0
0
0
0
0
111020218001
111020218001
287500
0
0
555838
555911
0
0
0
0
0
0
10433
10434
0
0
0
0
0
0
0
0
111020718002
111020718001
0
555812
555928
0
0
0
0
0
555943
555921
555946
555951
0
0
0
0
0
0
555915
555913
555918
555924
555925
555949
555950
555952
555953
0
0
0
0
0
111021218001
555956
555969
10435
10436
555912
555914
555916
555923
555929
555930
555932
555934
555935
555938
555942
555970
0
0
0
555926
555933
555940
555944
555948
555955
555959
555963
555968
555972
555975
555981
0
0
0
555894
555937
555939
555947
555967
555971
555976
555979
0
0
0
0
0
555936
555962
555974
555983
0
0
0
0
0
0
555945
555966
555977
555980
0
0
0
0
0
555917
555990
10438
555964
555965
555989
556005
556008
556011
556012
556013
0
0
0
0
0
111022118001
10437
555922
555960
555961
555973
555985
555986
555987
555988
555994
555995
555996
555997
556001
556002
556004
556006
0
0
0
183200
0
0
555931
555984
555991
555999
556003
0
0
0
0
111022318001
0
555998
0
0
0
0
0
0
10439
10440
555919
555954
555992
556007
0
0
0
202800
111020718007
0
0
0
0
0
0
111022818007
0
0

<colgroup><col></colgroup><tbody>
</tbody>

 

[Jonmo1]

Try

=SUMPRODUCT((LEN(CSPG_Customer_Reference)=5)*(LEFT(CSPG_Customer_Reference,2)="10"))

 

[1629dill]

Thanks