Cell Contains Value

[Tendresse]

Hi all,
I have a spreadsheet that looks like this:


A B
1 [drop down list]
..
..
4 Product Name Location
5 Product 1 Wharehouse - London
6 Product 1 Storage Room - London Office
7 Product 2 Goerge Street
8 Product 1 London Wharehouse

cell A1 contains a drop down list that has the following items: London,
Paris, NY
I would like to write a formula to count the numbers of product 1 where the
location CONTAINS the word listed in cell A1. For example: if the user
selects the word 'London' in cell A1, i want to be able to count the number
of product 1 located in London. In the example above the answer is 3.
I'm using the SumProduct function but i don't know how to refer to 'cell
contains':

=SUMPRODUCT(--(A5:A8="Product1"),--(B5:B8 "contains the word in cell A1"))

i tried using the wild card (*) but i didn't seem to get it right yet. Any
help will be much appreciated.
I'm using Excel 2003.

Tendresse

 

[Max]

One way:
=SUMPRODUCT(--(A5:A8="Product1"),--(ISNUMBER(SEARCH(A1,B5:B8))))

 

[Tendresse]

It worked like a beauty ... Thank you so much

One way:
=SUMPRODUCT(--(A5:A8="Product1"),--(ISNUMBER(SEARCH(A1,B5:B8))))

Hi all,
I have a spreadsheet that looks like this:


A B
1 [drop down list]
.
.
4 Product Name Location
5 Product 1 Wharehouse - London
6 Product 1 Storage Room - London Office
7 Product 2 Goerge Street
8 Product 1 London Wharehouse

cell A1 contains a drop down list that has the following items: London,
Paris, NY
I would like to write a formula to count the numbers of product 1 where the
location CONTAINS the word listed in cell A1. For example: if the user
selects the word 'London' in cell A1, i want to be able to count the number
of product 1 located in London. In the example above the answer is 3.
I'm using the SumProduct function but i don't know how to refer to 'cell
contains':

=SUMPRODUCT(--(A5:A8="Product1"),--(B5:B8 "contains the word in cell A1"))

i tried using the wild card (*) but i didn't seem to get it right yet. Any
help will be much appreciated.
I'm using Excel 2003.

Tendresse

 

[Max]

Welcome, Tendresse