SUMPRODUCT with 3 arrays not working

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!
 
D

Don Guillett

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"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top