Help with Complex SUMPRODUCT formula

[Murph]

I need some help with a complex SUMPRODUCT Formula I have. I'm using 5
different columns in the formula to come up with the data I need. the first
column is numerical data, and the other 4 are text data. For 3 of the
columns I want to calculate based on multiple values for the same column.
Here is the original formula that was working fine for me.
=SUMPRODUCT(--(Inventory!$A$3:$A$9501>=0),--(Inventory!$A$3:$A$9501<=7),
--(Inventory!$I$3:$I$9501=$A39),--(Inventory!$J$3:$J$9501=$B39),
--(Inventory!$K$3:$K$9501<>"Contested Claim
Adj"),--(Inventory!$K$3:$K$9501<>"Standard Claim
Adj"),--(Inventory!$K$3:$K$9501<>"RX Reimbursements
North"),--(Inventory!$K$3:$K$9501<>"Extra Mile
Adj"),--(Inventory!$K$3:$K$9501<>"PARIT
Adj"),--(Inventory!$K$3:$K$9501<>"Grievance/Appeals
Adj"),--(Inventory!$L$3:$L$9501="Employee Group 502"))

Now I have to add one more criteria. I want it to calculate the above where
column L aslo equals "Emp Grp Member Adj-BlueOptions/BlueCare 549". So at
the end of the formula I added this --(Inventory!$L$3:$L$9501="Emp Grp
Member Adj-BlueOptions/BlueCare 549"). But when I did this formula just
returned a value of '0' . So I'm stuck at how to get it to do this. Any
help would be great.

 

[Bob Phillips]

Brendan,

Problem is that if you just stick that test in, you are checking L3:L9501 to
be equal to value 1 and to value 2, which is not possible. You want an OR
condition

=SUMPRODUCT(--(Inventory!$A$3:$A$9501>=0),--(Inventory!$A$3:$A$9501<=7),--(I
nventory!$I$3:$I$9501=$A39),--(Inventory!$J$3:$J$9501=$B39),--(Inventory!$K$
3:$K$9501<>"Contested Claim Adj"),--(Inventory!$K$3:$K$9501<>"Standard Claim
Adj"),--(Inventory!$K$3:$K$9501<>"RX Reimbursements
North"),--(Inventory!$K$3:$K$9501<>"Extra Mile
Adj"),--(Inventory!$K$3:$K$9501<>"PARIT
Adj"),--(Inventory!$K$3:$K$9501<>"Grievance/Appeals
Adj"),--((Inventory!$L$3:$L$9501="Employee Group
502")+(Inventory!$L$3:$L$9501="Emp Grp Member Adj-BlueOptions/BlueCare
549")))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Domenic]

Also...

=SUMPRODUCT(--(Inventory!$A$3:$A$9501>=0),--(Inventory!$A$3:$A$9501<=7),-
-(Inventory!$I$3:$I$9501=$A39),--(Inventory!$J$3:$J$9501=$B39),--(1-ISNUM
BER(MATCH(Inventory!$K$3:$K$9501,{"Contested Claim Adj","Standard Claim
Adj","RX Reimbursements North","Extra Mile Adj","PARIT
Adj","Grievance/Appeals
Adj"},0))),--(ISNUMBER(MATCH(Inventory!$L$3:$L$9501,{"Employee Group
502","Emp Grp Member Adj-BlueOptions/BlueCare 549"},0))))

OR

=SUMPRODUCT(--(Inventory!$A$3:$A$9501>=0),--(Inventory!$A$3:$A$9501<=7),-
-(Inventory!$I$3:$I$9501=$A39),--(Inventory!$J$3:$J$9501=$B39),--(1-ISNUM
BER(MATCH(Inventory!$K$3:$K$9501,$A$1:$A$6,0))),--(ISNUMBER(MATCH(Invento
ry!$L$3:$L$9501,$B$1:$B$2,0))))

....where A1:A6 contains your list of criteria for Column K, and B1:B2
contains your list of criteria for Column L. You can easily add more
criteria to your lists and change the references accordingly.

Hope this helps!

 

[Murph]

Thanks for that Domenic. That worked great. I've never used that ISNUMBER
and MATCH formula. Is there a good resource that explains how those work?

 

[Bob Phillips]

MATCH is just looking up a value in an array. It will either find or not,
ISNUMBER is then used to return that found or not as TRUE or FALSE which the
double unary then coerces to 1 or 0 as standard.

It's unlikely you will find anywhere that explains that, as it is the
amalgam of the functions that solves a particular problem, and the problems
are infinite. The way that I am sure people like Domenic learn it is by
getting a good understanding of how the good functions work (MATCH, INDEX,
CHOOSE, INDIRECT, ISNUMBER, etc.), and then apply them, read up on others
solutions, and work them through. In other words, experience, and a creative
flair.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Thanks for that Domenic. That worked great. I've never used that ISNUMBER
and MATCH formula. Is there a good resource that explains how those work?

Also...

=SUMPRODUCT(--(Inventory!$A$3:$A$9501>=0),--(Inventory!$A$3:$A$9501<=7),-

-(Inventory!$I$3:$I$9501=$A39),--(Inventory!$J$3:$J$9501=$B39),--(1-ISNUM
BER(MATCH(Inventory!$K$3:$K$9501,{"Contested Claim Adj","Standard Claim
Adj","RX Reimbursements North","Extra Mile Adj","PARIT
Adj","Grievance/Appeals
Adj"},0))),--(ISNUMBER(MATCH(Inventory!$L$3:$L$9501,{"Employee Group
502","Emp Grp Member Adj-BlueOptions/BlueCare 549"},0))))

OR

=SUMPRODUCT(--(Inventory!$A$3:$A$9501>=0),--(Inventory!$A$3:$A$9501<=7),-

 

[Domenic]

The way that I am sure people like Domenic learn it is by
getting a good understanding of how the good functions work (MATCH, INDEX,
CHOOSE, INDIRECT, ISNUMBER, etc.), and then apply them, read up on others
solutions, and work them through. In other words, experience, and a creative
flair.

Hi Bob! That's exactly right!

I've learned, and continue to learn from people like you and others who
have extensive experience.

Cheers!