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

One way:
=SUMPRODUCT(--(A5:A8="Product1"),--(ISNUMBER(SEARCH(A1,B5:B8))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tendresse" wrote:
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

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



"Max" wrote:

One way:
=SUMPRODUCT(--(A5:A8="Product1"),--(ISNUMBER(SEARCH(A1,B5:B8))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tendresse" wrote:
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

Welcome, Tendresse
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tendresse" wrote in message
...
It worked like a beauty ... Thank you so much :)