| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use OR function here
I have the formula below but now need to check in this same formula for two
to three additional abbreviation. I need it to check on ex, ia, la and rp, they need to be counted together. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) In another column I also need to exclude one abbreviation. I need to exclude on abbreviation which in full is OPSB. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$J$2:$J$991=$B2)) Can someone help me plse. Regards, Lupe |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I use OR function here
Q1: In
=SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) use --(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"}) Q2:=SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$I$2:$I$991<"opsb"),--(downloaded!$J$2:$J$991=$B2)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lupe" wrote in message ... I have the formula below but now need to check in this same formula for two to three additional abbreviation. I need it to check on ex, ia, la and rp, they need to be counted together. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) In another column I also need to exclude one abbreviation. I need to exclude on abbreviation which in full is OPSB. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$J$2:$J$991=$B2)) Can someone help me plse. Regards, Lupe |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I use OR function here
Bernard, Thanks for taking the time to answer my question. Appreciate it.
What you gave me for Q2 works very good. But for Q1 it is ignoring the 2nd criteria. The formula has to check also on the criterias I have in column B which changes with each row. Example: staff and labor are criterias in column B. It has to check how many persons in department starting with"ex","ia","la","rp" are staff and how many are labor. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"}))--(downloaded!$J$2:$J$991=$B2) Regards, Lupe "Bernard Liengme" wrote: Q1: In =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) use --(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"}) Q2:=SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$I$2:$I$991<"opsb"),--(downloaded!$J$2:$J$991=$B2)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lupe" wrote in message ... I have the formula below but now need to check in this same formula for two to three additional abbreviation. I need it to check on ex, ia, la and rp, they need to be counted together. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) In another column I also need to exclude one abbreviation. I need to exclude on abbreviation which in full is OPSB. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$J$2:$J$991=$B2)) Can someone help me plse. Regards, Lupe |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I use OR function here
If your really have
=SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"})) --(downloaded!$J$2:$J$991=$B2) you are missing a comma before the second -- =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"})), --(downloaded!$J$2:$J$991=$B2) let me know if this is the problem -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lupe" wrote in message ... Bernard, Thanks for taking the time to answer my question. Appreciate it. What you gave me for Q2 works very good. But for Q1 it is ignoring the 2nd criteria. The formula has to check also on the criterias I have in column B which changes with each row. Example: staff and labor are criterias in column B. It has to check how many persons in department starting with"ex","ia","la","rp" are staff and how many are labor. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"}))--(downloaded!$J$2:$J$991=$B2) Regards, Lupe "Bernard Liengme" wrote: Q1: In =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) use --(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"}) Q2:=SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$I$2:$I$991<"opsb"),--(downloaded!$J$2:$J$991=$B2)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lupe" wrote in message ... I have the formula below but now need to check in this same formula for two to three additional abbreviation. I need it to check on ex, ia, la and rp, they need to be counted together. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) In another column I also need to exclude one abbreviation. I need to exclude on abbreviation which in full is OPSB. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$J$2:$J$991=$B2)) Can someone help me plse. Regards, Lupe |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I use OR function here
The "--" syntax won't work if arrays are of different sizes, try
instead: =SUMPRODUCT((LEFT(downloaded!$I$2:$I$991,2)={"ex", "ia","la","rp"}) *(downloaded!$J$2:$J$991=$B2)) On Jan 24, 3:33 pm, "Bernard Liengme" wrote: If your really have =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"})) --(downloaded!$J$2:$J$991=$B2) you are missing a comma before the second -- =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"})), --(downloaded!$J$2:$J$991=$B2) let me know if this is the problem -- Bernard V Liengmewww.stfx.ca/people/bliengme remove caps from email "Lupe" wrote in ... Bernard, Thanks for taking the time to answer my question. Appreciate it. What you gave me for Q2 works very good. But for Q1 it is ignoring the 2nd criteria. The formula has to check also on the criterias I have in column B which changes with each row. Example: staff and labor are criterias in column B. It has to check how many persons in department starting with"ex","ia","la","rp" are staff and how many are labor. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"}))--(downloaded!$J$2:$J$991=$B2) Regards, Lupe "Bernard Liengme" wrote: Q1: In =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) use --(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"}) Q2:=SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$I$2:$I$991<"opsb"),--(downloaded!$J$2:$J$991=$B2)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lupe" wrote in message ... I have the formula below but now need to check in this same formula for two to three additional abbreviation. I need it to check on ex, ia, la and rp, they need to be counted together. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) In another column I also need to exclude one abbreviation. I need to exclude on abbreviation which in full is OPSB. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$J$2:$J$991=$B2)) Can someone help me plse. Regards, Lupe |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can I use OR function here
Thanks, Lori.
Bernard, Lori's formula worked, so the * fixed it. Thanks to both of you. Regards, Lupe "Lori" wrote: The "--" syntax won't work if arrays are of different sizes, try instead: =SUMPRODUCT((LEFT(downloaded!$I$2:$I$991,2)={"ex", "ia","la","rp"}) *(downloaded!$J$2:$J$991=$B2)) On Jan 24, 3:33 pm, "Bernard Liengme" wrote: If your really have =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"})) --(downloaded!$J$2:$J$991=$B2) you are missing a comma before the second -- =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"})), --(downloaded!$J$2:$J$991=$B2) let me know if this is the problem -- Bernard V Liengmewww.stfx.ca/people/bliengme remove caps from email "Lupe" wrote in ... Bernard, Thanks for taking the time to answer my question. Appreciate it. What you gave me for Q2 works very good. But for Q1 it is ignoring the 2nd criteria. The formula has to check also on the criterias I have in column B which changes with each row. Example: staff and labor are criterias in column B. It has to check how many persons in department starting with"ex","ia","la","rp" are staff and how many are labor. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"}))--(downloaded!$J$2:$J$991=$B2) Regards, Lupe "Bernard Liengme" wrote: Q1: In =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) use --(LEFT(downloaded!$I$2:$I$991,2)={"ex","ia","la","r p"}) Q2:=SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$I$2:$I$991<"opsb"),--(downloaded!$J$2:$J$991=$B2)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lupe" wrote in message ... I have the formula below but now need to check in this same formula for two to three additional abbreviation. I need it to check on ex, ia, la and rp, they need to be counted together. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,2)="ex"),--(downloaded!$J$2:$J$991=$B3)) In another column I also need to exclude one abbreviation. I need to exclude on abbreviation which in full is OPSB. =SUMPRODUCT(--(LEFT(downloaded!$I$2:$I$991,1)="o"),--(downloaded!$J$2:$J$991=$B2)) Can someone help me plse. Regards, Lupe |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
| Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Linear Mode
