K
Kierano
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!
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!