Sum product -Is column value in range/list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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