Help with Complex SUMPRODUCT formula

M

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.
 
B

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

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

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?
 
B

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)


Murph said:
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?

Domenic said:
=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),-
 
D

Domenic

Bob Phillips said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top