SumProduction function - multiple worksheets

[cyruslwc828]

Hi all,

I now have two worksheets, one containing usage of materials of a product. (Part no. in column A and usage in column B)

The other worksheet I have contains purchase price of materials (Part no in column A and price in column B)

I tried to use the sumproduction function to calculate the total cost of a product by multiplying usage of parts and part price and adding up all parts used.

However, i found that when it comes to parts with asterid (e.g. Part A and Part A*), the formula is always matching Part A* as if it is Part A.

Can anyone help to address how should i write the formula to distinguish the two?

Thanks,
Cyrus

 

[KolGuyXcel]

It seems to me that you have repetitive occurrence of the "Part no.s" in column A of either sheet! Is that so?

Does it have to be a formula solution that you seek or could you use a code?

 

[cyruslwc828]

"It seems to me that you have repetitive occurrence of the "Part no.s" in column A of either sheet! Is that so?"

Column A (part no.) for both sheets are identical and each part will only appear once in column A.

"Does it have to be a formula solution that you seek or could you use a code?"

I would prefer to use formula because I can still comprehend but for code, i have absolute zero knowledge.

Thanks =]

 

[FormR]

However, i found that when it comes to parts with asterid (e.g. Part A and Part A*), the formula is always matching Part A* as if it is Part A.

Hi, welcome to the board!

You would need to show us your formula but SUMPRODUCT() does not generally work with wildcard characters so should treat them separately. Perhaps you meant SUMIFS() - either way, here is an option you can try.

Excel 2013/2016
	A	B	C
1	Part	Price	Total Cost
2	Part A	10.99	131.88
3	Part A*	20.99	209.9
Sheet2

Cell Formulas
Range	Formula
C2	=SUMIFS(Sheet1!B:B,Sheet1!A:A,SUBSTITUTE(A2,"*","~*"))*B2

Excel 2013/2016
	A	B
1	Part	Usage
2	Part A	8
3	Part A*	4
4	Part A	4
5	Part A*	6
Sheet1

 

[cyruslwc828]

It worked~

Thanks a lot~