Sumproduct-multiple criteria = and not =

[Tasha]

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=SHEET1!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.

 

[Per Jessen]

Hi

Try this (should be entered as one line):

=SUMPRODUCT((Book1!ADMFIN="B")*(Book1!ADMDAY=Sheet1!E$3))-SUMPRODUCT((Book1!ADMFIN="B")*(Book1!ADMDAY=Sheet1!E$3)*(Book1!ADMHSV="NUR"))

Regards,
Per

 

[Tasha]

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

 

[Tasha]

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?

 

[Spiky]

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<>"SWG")*(ADMHSV<>"NUR")*(ADMDAY=E
$3)*(ISNUMBER(MATCH(ADMFIN,{"F";"H";"N"},0))))