Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct formulas not working after editing | Excel Worksheet Functions | |||
Sumproduct formulas not working after editing | Excel Worksheet Functions | |||
amount of working days per month | Excel Discussion (Misc queries) | |||
If condition is true return sumproduct of two arrays | Excel Worksheet Functions | |||
If condition is true return sumproduct of two arrays | Excel Worksheet Functions |