SUMPRODUCT with 3 arrays not working
 

I want to calculate something based on 3 criteria.

I have 3 columns:

column 1 has RAG status, so contains reds, ambers, greens
column 2 has types, so contains 1, 2, 3 and 4
column 3 has projects, so contains A1-AAA, B2-CCC, Z1-DD2 etc. (so 6
characters)

I want to be able to calculate all type 1s with Green status beginning with B.

I have tried the following formula, but to no avail:

=SUMPRODUCT(--($A$9:$A$85="Green"),--(LEFT($C$9:$C$85)="1"),--(LEFT($D$9:$D$85,6)="B*"))

The annoying thing is, if I state the exact ID of the project, it picks it up!

SUMPRODUCT with 3 arrays not working
 

try
=SUMPRODUCT(--($A$9:$A$85="Green"),--($C$9:$C$85)=1),--(LEFT($D$9:$D$85,1)="B"))
or
=SUMPRODUCT(--($A$9:$A$85="Green"),--(LEFT($C$9:$C$85)="1"),--(LEFT($D$9:$D$85,1)="B"))


--
Don Guillett
SalesAid Software

"Kierano" wrote in message
...
I want to calculate something based on 3 criteria.

I have 3 columns:

column 1 has RAG status, so contains reds, ambers, greens
column 2 has types, so contains 1, 2, 3 and 4
column 3 has projects, so contains A1-AAA, B2-CCC, Z1-DD2 etc. (so 6
characters)

I want to be able to calculate all type 1s with Green status beginning
with B.

I have tried the following formula, but to no avail:

=SUMPRODUCT(--($A$9:$A$85="Green"),--(LEFT($C$9:$C$85)="1"),--(LEFT($D$9:$D$85,6)="B*"))

The annoying thing is, if I state the exact ID of the project, it picks it
up!