isnumber with sumproduct

[sh0t2bts]

Hi

I have a am trying to count occurrences of someone entering data into my
table.
The below function counts every occurrence where Maximo A2 to A1398 =
SFlintstone and the first two characters in column D are
OS.=SUMPRODUCT((Maximo!$D$2:$D$1398=$B4)*(1*(LEFT(Maximo!$A$2:$A$1398,2)="OS
")))

I now want to make the same match but where character 2 to 4 are numbers in
column D
This function will bring back if the column is not blank but it counts
characters as well as numbers.
=SUMPRODUCT((Maximo!$D$2:$D$1398=$B4)*(1*(MID(Maximo!$A$2:$A$1398,2,4)>"0"))
)


The data in column D is as follows:-
E1254D
A0120Z
BAY102
RM2893
OS353

what I want to do is return 2 as only the first two entries match my
requirements


Hopefully this makes sense

Many Thanks

Mark

 

[Bob Phillips]

Give this a try

=SUMPRODUCT(--(Maximo!$D$2:$D$1398=$B4),--(ISNUMBER(--MID(Maximo!$A$2:$A$130
9,2,4))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

try incorporating this idea
=ISNUMBER(VALUE(MID(A15,2,4)))