isnumber with sumproduct
 

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(Maxi mo!$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

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)


"sh0t2bts" wrote in message
...
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(Maxi mo!$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

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

--
Don Guillett
SalesAid Software

"sh0t2bts" wrote in message
...
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(Maxi mo!$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