Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct-multiple criteria = and not =
Ok....this is my problem.... have multiple criteria that I need to set up a
SUMPRODUCT function for, equalling some and not equalling some. This is one example: Range names: sheet2-ADMDAY,ADMFIN,ADMHSV formula example for range names: ADMDAY =OFFSET(sheet2!$B$2,0,0,COUNTA(sheet2!$B:$B),1) What I need: Sheet 1, cell E4 ADMDAY=E3, ADMFIN="M" AND "MG", ADMHSV not = "NUR" My formula: =SUMPRODUCT((sheet2!ADMFIN="B")*(SHEET2!ADMDAY=SHE ET1!E$3)*(ISNA(MATCH(sheet2!ADMHSV,{"NUR"},0)))) hoping someone can help me, have tried doing the sumproduct seperated, but then ran into a problem when I didn't want to include those with NUR. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct-multiple criteria = and not =
Hi
Try this (should be entered as one line): =SUMPRODUCT((Book1!ADMFIN="B")*(Book1!ADMDAY=Sheet 1!E$3))-SUMPRODUCT((Book1!ADMFIN="B")*(Book1!ADMDAY=Sheet1 !E$3)*(Book1!ADMHSV="NUR")) Regards, Per "Tasha" skrev i meddelelsen ... Ok....this is my problem.... have multiple criteria that I need to set up a SUMPRODUCT function for, equalling some and not equalling some. This is one example: Range names: sheet2-ADMDAY,ADMFIN,ADMHSV formula example for range names: ADMDAY =OFFSET(sheet2!$B$2,0,0,COUNTA(sheet2!$B:$B),1) What I need: Sheet 1, cell E4 ADMDAY=E3, ADMFIN="M" AND "MG", ADMHSV not = "NUR" My formula: =SUMPRODUCT((sheet2!ADMFIN="B")*(SHEET2!ADMDAY=SHE ET1!E$3)*(ISNA(MATCH(sheet2!ADMHSV,{"NUR"},0)))) hoping someone can help me, have tried doing the sumproduct seperated, but then ran into a problem when I didn't want to include those with NUR. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct-multiple criteria = and not =
Well, tried something similar, but did not want to include those with "NUR".
When I tried it, got 0. Actually, just got this formula and it worked... =SUMPRODUCT((ADMHSV<"NUR")*(ADMDAY=E$3)*(ISNUMBER (MATCH(ADMFIN,{"F";"H";"N"},0)))) thanks for your reply!!! "Per Jessen" wrote: Hi Try this (should be entered as one line): =SUMPRODUCT((Book1!ADMFIN="B")*(Book1!ADMDAY=Sheet 1!E$3))-SUMPRODUCT((Book1!ADMFIN="B")*(Book1!ADMDAY=Sheet1 !E$3)*(Book1!ADMHSV="NUR")) Regards, Per "Tasha" skrev i meddelelsen ... Ok....this is my problem.... have multiple criteria that I need to set up a SUMPRODUCT function for, equalling some and not equalling some. This is one example: Range names: sheet2-ADMDAY,ADMFIN,ADMHSV formula example for range names: ADMDAY =OFFSET(sheet2!$B$2,0,0,COUNTA(sheet2!$B:$B),1) What I need: Sheet 1, cell E4 ADMDAY=E3, ADMFIN="M" AND "MG", ADMHSV not = "NUR" My formula: =SUMPRODUCT((sheet2!ADMFIN="B")*(SHEET2!ADMDAY=SHE ET1!E$3)*(ISNA(MATCH(sheet2!ADMHSV,{"NUR"},0)))) hoping someone can help me, have tried doing the sumproduct seperated, but then ran into a problem when I didn't want to include those with NUR. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct-multiple criteria = and not =
well, have now run into another problem. Don't want ADMHSV to equal NUR or
SWG, but do want ADMDAY to equal E3 and ADMFIN to equal "M" and "MG". Does anyone know how to do that? "Tasha" wrote: Ok....this is my problem.... have multiple criteria that I need to set up a SUMPRODUCT function for, equalling some and not equalling some. This is one example: Range names: sheet2-ADMDAY,ADMFIN,ADMHSV formula example for range names: ADMDAY =OFFSET(sheet2!$B$2,0,0,COUNTA(sheet2!$B:$B),1) What I need: Sheet 1, cell E4 ADMDAY=E3, ADMFIN="M" AND "MG", ADMHSV not = "NUR" My formula: =SUMPRODUCT((sheet2!ADMFIN="B")*(SHEET2!ADMDAY=SHE ET1!E$3)*(ISNA(MATCH(sheet2!ADMHSV,{"NUR"},0)))) hoping someone can help me, have tried doing the sumproduct seperated, but then ran into a problem when I didn't want to include those with NUR. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct-multiple criteria = and not =
Just put each one in there. If these are all AND (they must all be
true), then keep using the * to separate each relationship. If any are an OR, use + to separate. =SUMPRODUCT((ADMFIN="MG")*(ADMFIN="M")*(ADMHSV<"S WG")*(ADMHSV<"NUR")*(ADMDAY=E $3)*(ISNUMBER(MATCH(ADMFIN,{"F";"H";"N"},0)))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
HELP...SUMPRODUCT with multiple criteria | Excel Discussion (Misc queries) | |||
SUMPRODUCT with Multiple Criteria | Excel Worksheet Functions | |||
SumProduct With Multiple criteria | Excel Worksheet Functions | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions |