Sum for multi variable data validation

[jjalyss]

I have created a spreadsheet to track apparel orders. In one column I have the products in a drop down menu, and can select multiple products from the list in one cell separated by a comma.

Eg. red shirt, blue shirt, green shirt

I would like to know how i could generate a sum for each product

eg: green shirt: 5
blue shirt: 7

I'd also like to know how to add a column next to products with a sum of the pricing for each product per row

 

[Akashwani]

Hi,

Welcome to MrExcel.

To get a reply that may result in a solution I think that you will have to provide some sample data.

1. What is the range that you have your "shirts" in?
2. Is each "shirt" colour in a separate cell?

Here's some sample data so that you understand what you need to provide...

Excel Workbook
	A	B	C	D	E	F	G
1	Shirt Type	Value		red shirt, green shirt, blue shirt	red shirt	green shirt	blue shirt
2	Red Shirt	10.00		Count	4	3	2
3	Green Shirt	5.00		Value	40.00	15.00	12.00
4	Polkadot Shirt	0.02
5	Loud Shirt	20.00
6	Orange Shirt	0.01		Polkadot Shirt, Red shirt, Loud shirt	Polkadot Shirt	Red shirt	Loud shirt
7	Blue Shirt	6.00		Count	1	4	2
8	Red Shirt	10.00		Value	0.02	40.00	40.00
9	Green Shirt	5.00
10	Blue Shirt	6.00
11	Red Shirt	10.00
12	Orange Shirt	0.01
13	Loud Shirt	20.00
14	Red Shirt	10.00
15	Green Shirt	5.00
Sheet1

Is that something like you have/want?

Ak

 

[jjalyss]

So I have it set up like columns A and B with product name and price. Then on a separate sheet I have something like:

Column A: Customer Name
Column B: Products (here I can place multiple variables)

Then I just need an area where it will sum column B based on the Product quantity, and then the price.

 

[Akashwani]

Hi,

Some sample data would be really helpful, rather than "Then on a separate sheet I have something like".....

Is this something like you require?....

Excel Workbook
	A	B
1	Product Name	Price
2	Blue Shirt	6.00
3	Green Shirt	5.00
4	Loud Shirt	20.00
5	Orange Shirt	0.01
6	Polkadot Shirt	0.02
7	Red Shirt	10.00
Sheet1

Excel Workbook
	A	B	C	D	E	F
1	Customer Name	Products	*	Products	Count	Value
2	Bill	Red Shirt, Green Shirt, Blue Shirt	*	Blue Shirt	9	54.00
3	Bob	Blue Shirt, Loud Shirt, Orange Shirt	*	Green Shirt	5	25.00
4	Tom	Green Shirt, Blue Shirt	*	Loud Shirt	2	40.00
5	Jo	Red Shirt	*	Orange Shirt	4	0.04
6	Joe	Orange Shirt, Red Shirt, Polkadot Shirt, Blue Shirt	*	Polkadot Shirt	3	0.06
7	Tim	Green Shirt * , Blue Shirt,,Loud Shirt	*	Red Shirt	8	80.00
8	Dan	Red Shirt	*	*	*	*
9	Jill	Orange Shirt, Red Shirt, Polkadot Shirt, Blue Shirt	*	*	*	*
10	Ted	Green Shirt *, Blue Shirt	*	*	*	*
11	John	* * * * Red Shirt	*	*	*	*
12	Ben	Orange Shirt, Red Shirt, Polkadot Shirt, Blue Shirt	*	*	*	*
13	Den	Green Shirt-Blue Shirt * *Red Shirt	*	*	*	*
Sheet2

Is that the result you require?

Ak

 

[jjalyss]

That would be it exactly! Thanks so much