Cell Contains Value

T

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
 
T

Tendresse

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



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

---
Tendresse said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple text function calculation 5
get value of cell only 6
How to determine the value within specific cell position? 5
cell B1 contains a word listed in cell A1 2
SUMPRODUCT with "Contains" criteria 9
Creating a Data Validation List based on a Value in another cell 2
Vlookup Help 5
Cell Contains Word 4

Top