G
Guest
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.
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.