Sum product -Is column value in range/list
 

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!

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!

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!

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

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