sumproduct three conditions

Need to count the number of time that first 3 characters in col a appear,
based on col b >0 and col c<1.
The following formula is not working, the formula is not finding AAS:
=SUMPRODUCT($A$4:$A$2002="aas*",$B$4:$B$2002>0,$D$4:$D$2002<1)

Data:
A B C D
AASR241 1 1 1
AASR340 1 1 0
AASR444 1 1 0
CAP1201 1 1 0

the results should be 2.

Thanks in advance.

Try this:

A1: AAS
B1: =SUMPRODUCT((LEFT(A4:A2002,LEN(A1))=A1)*(B4:B2002>0)*(D4:D2002<1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP-Pro


"Scire" wrote:

> Need to count the number of time that first 3 characters in col a appear,
> based on col b >0 and col c<1.
> The following formula is not working, the formula is not finding AAS:
> =SUMPRODUCT($A$4:$A$2002="aas*",$B$4:$B$2002>0,$D$4:$D$2002<1)
>
> Data:
> A B C D
> AASR241 1 1 1
> AASR340 1 1 0
> AASR444 1 1 0
> CAP1201 1 1 0
>
> the results should be 2.
>
> Thanks in advance.

Try...

=SUMPRODUCT(--(LEFT($A$4:$A$2002,3)="AAS"),--($B$4:$B$2002>0),--($D$4:$D$
2002<1))

Hope this helps!

In article <[email protected]>,
Scire <[email protected]> wrote:

> Need to count the number of time that first 3 characters in col a appear,
> based on col b >0 and col c<1.
> The following formula is not working, the formula is not finding AAS:
> =SUMPRODUCT($A$4:$A$2002="aas*",$B$4:$B$2002>0,$D$4:$D$2002<1)
>
> Data:
> A B C D
> AASR241 1 1 1
> AASR340 1 1 0
> AASR444 1 1 0
> CAP1201 1 1 0
>
> the results should be 2.
>
> Thanks in advance.

Thanks.

"Domenic" wrote:

> Try...
>
> =SUMPRODUCT(--(LEFT($A$4:$A$2002,3)="AAS"),--($B$4:$B$2002>0),--($D$4:$D$
> 2002<1))
>
> Hope this helps!
>
> In article <[email protected]>,
> Scire <[email protected]> wrote:
>
> > Need to count the number of time that first 3 characters in col a appear,
> > based on col b >0 and col c<1.
> > The following formula is not working, the formula is not finding AAS:
> > =SUMPRODUCT($A$4:$A$2002="aas*",$B$4:$B$2002>0,$D$4:$D$2002<1)
> >
> > Data:
> > A B C D
> > AASR241 1 1 1
> > AASR340 1 1 0
> > AASR444 1 1 0
> > CAP1201 1 1 0
> >
> > the results should be 2.
> >
> > Thanks in advance.
>