SUMPRODUCT with 3 criteria
 

I have 3 columns with the following data in:

RAG status e.g. red, amber, green
Types e.g. 1, 2, 3, 4
Project e.g. A1-AA1, A2-BC3, B1-HH1, B2-JK2, B3-JJ2

I want to be able to calculate all type 1s that are Green beginning with A
or B etc.

I have tried using the following formula:

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

Frustratingly, if I put the full name of the project in, the formula works,
but when I use wildcards it doesn't.

SUMPRODUCT with 3 criteria
 

See your other post

--
Don Guillett
SalesAid Software

"Kierano" wrote in message
...
I have 3 columns with the following data in:

RAG status e.g. red, amber, green
Types e.g. 1, 2, 3, 4
Project e.g. A1-AA1, A2-BC3, B1-HH1, B2-JK2, B3-JJ2

I want to be able to calculate all type 1s that are Green beginning with A
or B etc.

I have tried using the following formula:

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

Frustratingly, if I put the full name of the project in, the formula
works,
but when I use wildcards it doesn't.