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.

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.

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.

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.

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