Help with Complex SUMPRODUCT formula

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

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)


"Murph" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> Brendan
> --
> Brendan

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!

In article <[email protected]>,
"Murph" <[email protected]> wrote:

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

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" wrote:

> 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!
>
> In article <[email protected]>,
> "Murph" <[email protected]> wrote:
>
> > 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.
> > --
> > Brendan
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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!
> >
> > In article <[email protected]>,
> > "Murph" <[email protected]> wrote:
> >
> > > 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.
> > > --
> > > Brendan
> >

In article <[email protected]>,
"Bob Phillips" <[email protected]> wrote:

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