Sum product -Is column value in range/list

[confused]

Hi,

How can I check to see if a value in a column is contained within a list or
range?
I want to add up only those rows which match the following criteria
If column L = "LDC"
AND Column B is in a list or range of values
THEN add up add up the Column C values for these rows.

I am using sum product but cant work out how to check if column B is in a
range or list.
My pseudo code is below:

=SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN RANGE H1:H100)
OR
=SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN LIST {name1, name2,
name3...})

would like to know how to do it both ways, but especially the first 'in
range' way as I have a work around for the second using the OR function

Thanks very much for any help
cheers!

[bj]

try
=SUMPRODUCT(--( L2:L18 =
"LDC"),(C2:C18),--(vlookup(B2:B18,$H$1:$H$100,1,false)))
"confused" wrote:

> Hi,
>
> How can I check to see if a value in a column is contained within a list or
> range?
> I want to add up only those rows which match the following criteria
> If column L = "LDC"
> AND Column B is in a list or range of values
> THEN add up add up the Column C values for these rows.
>
> I am using sum product but cant work out how to check if column B is in a
> range or list.
> My pseudo code is below:
>
> =SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN RANGE H1:H100)
> OR
> =SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN LIST {name1, name2,
> name3...})
>
> would like to know how to do it both ways, but especially the first 'in
> range' way as I have a work around for the second using the OR function
>
> Thanks very much for any help
> cheers!

[confused]

hi,

thanks for your reply! i tried as you suggest but get a #Value error.
It is occurring at the B2:B18 part, could this be because this should be a
single value here rather than a range? any ideas?

Thanks!

"bj" wrote:

> try
> =SUMPRODUCT(--( L2:L18 =
> "LDC"),(C2:C18),--(vlookup(B2:B18,$H$1:$H$100,1,false)))
> "confused" wrote:
>
> > Hi,
> >
> > How can I check to see if a value in a column is contained within a list or
> > range?
> > I want to add up only those rows which match the following criteria
> > If column L = "LDC"
> > AND Column B is in a list or range of values
> > THEN add up add up the Column C values for these rows.
> >
> > I am using sum product but cant work out how to check if column B is in a
> > range or list.
> > My pseudo code is below:
> >
> > =SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN RANGE H1:H100)
> > OR
> > =SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN LIST {name1, name2,
> > name3...})
> >
> > would like to know how to do it both ways, but especially the first 'in
> > range' way as I have a work around for the second using the OR function
> >
> > Thanks very much for any help
> > cheers!

[Domenic]

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B18,H1:H100,0))),C2:C18,--(
L2:L18="LDC"))

OR

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B18,{"Name1","Name2","Name3"},0))),C2:C1
8,--( L2:L18="LDC"))

Hope this helps!

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

> Hi,
>
> How can I check to see if a value in a column is contained within a list or
> range?
> I want to add up only those rows which match the following criteria
> If column L = "LDC"
> AND Column B is in a list or range of values
> THEN add up add up the Column C values for these rows.
>
> I am using sum product but cant work out how to check if column B is in a
> range or list.
> My pseudo code is below:
>
> =SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN RANGE H1:H100)
> OR
> =SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN LIST {name1, name2,
> name3...})
>
> would like to know how to do it both ways, but especially the first 'in
> range' way as I have a work around for the second using the OR function
>
> Thanks very much for any help
> cheers!

[confused]

dominics suggestion works, thanks to both of you!!!

"Domenic" wrote:

> Try...
>
> =SUMPRODUCT(--(ISNUMBER(MATCH(B2:B18,H1:H100,0))),C2:C18,--(
> L2:L18="LDC"))
>
> OR
>
> =SUMPRODUCT(--(ISNUMBER(MATCH(B2:B18,{"Name1","Name2","Name3"},0))),C2:C1
> 8,--( L2:L18="LDC"))
>
> Hope this helps!
>
> In article <[email protected]>,
> "confused" <[email protected]> wrote:
>
> > Hi,
> >
> > How can I check to see if a value in a column is contained within a list or
> > range?
> > I want to add up only those rows which match the following criteria
> > If column L = "LDC"
> > AND Column B is in a list or range of values
> > THEN add up add up the Column C values for these rows.
> >
> > I am using sum product but cant work out how to check if column B is in a
> > range or list.
> > My pseudo code is below:
> >
> > =SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN RANGE H1:H100)
> > OR
> > =SUMPRODUCT( L2:L18 = "LDC")*(C2:C18)*(B2:B18 IS IN LIST {name1, name2,
> > name3...})
> >
> > would like to know how to do it both ways, but especially the first 'in
> > range' way as I have a work around for the second using the OR function
> >
> > Thanks very much for any help
> > cheers!
>