lookup 2 columns

  • Thread starter Thread starter dalandnat
  • Start date Start date
D

dalandnat

Hi I'm trying to collate some data depending if some specific words
are in 2 colums. I have one column with just one word to lookup which
works, but the other column has many words and I want it to return the
data from a third column depending if a specific word is in the 2nd
column. I have used SUMIF but it doesn't return any data as I think it
is looking for that word only, however there are many words in the
column.

can anyone help please?

Many thanks!
 
Since you mentioned SUMIF that suggests you want a conditional sum but your
description sounds more like you want a conditional count.

I think a better more detailed explanation is needed. Tell us exactly where
the data is. Tell us what the "words" are. Give a small example.
 
Since you mentioned SUMIF that suggests you want a conditional sum but your
description sounds more like you want a conditional count.

I think a better more detailed explanation is needed. Tell us exactly where
the data is. Tell us what the "words" are. Give a small example.

--
Biff
Microsoft Excel MVP








- Show quoted text -
Click to expand...

Hi thanks for the reply.

In the first column I have a one word description which could be
shirt, pants, shoes etc. In the second column I have the description
of a product which could be "ladies t-shirt", "mens jeans' etc. and in
the 3rd colum I have the amount sold. So what I want to do is create a
formula to add up all of sales in colum 3 depending on the criteria of
say 'ladies shirts, being the shirt from the first colum and finding
the word ladies in the second. I have tried sumif but it doesn't find
'ladies' in the second column as there is more text in the cell than
just ladies, does that make sense?

Thanks again!
 
Hi thanks for the reply.

In the first column I have a one word description which could be
shirt, pants, shoes etc. In the second column I have the description
of a product which could be "ladies t-shirt", "mens jeans' etc. and in
the 3rd colum I have the amount sold. So what I want to do is create a
formula to add up all of sales in colum 3 depending on the criteria of
say 'ladies shirts, being the shirt from the first colum and finding
the word ladies in the second. I have tried sumif but it doesn't find
'ladies' in the second column as there is more text in the cell than
just ladies, does that make sense?

Thanks again!
Click to expand...

One way, using a helper column...

If D1:F2 and Column G are available

Put heading in D1 "Col A is..."
Use D2 for typing appropriate Column A criterion eg shirt, pants,
shoes etc

Put heading in E1 "Col B has..."
Use E2 for typing appropriate Column B criterion eg ladies, mens etc

Put heading "Sales.." in F1
Put following formula in F2...

=SUMPRODUCT(--(A2:A1000=$D$2),--(G2:G1000),(C2:C1000))

Put following formula in G2 then fill down to G1000...

=COUNTIF(B2,"*" & $E$2&"*")>0

Adjust row numbers to suit the extent of your data.

Ken Johnson
 
Back
Top