Sum required for Excel 2003

In the attached spreadsheet in worksheet 'Value' is already some sums which work out the sales value for each product line.  Using the same theory as as in the current sums I would like to starting in cell c17 work out the cost price of these sales by multiplying the quantity sold by the cost price in column B.

Thanks

Simon
Sales-Commissions2.xls
Who is Participating?

Commented:
Hello Simon,

Perhaps you need this version in C17 copied across and down

=\$B17*SUMPRODUCT((Sales!\$L\$2:\$L\$100=\$B\$1)*(Sales!\$I\$2:\$I\$100=VLOOKUP(\$A3,'Ink Nominals'!\$A\$1:\$B\$8,2,0)&"")*(TEXT(C\$1,"mmm-yy")=TEXT(Sales!\$B\$2:\$B\$100,"mmm-yy")),Sales!\$F\$2:\$F\$100)

....although if you removed the unit price part from the first formula (in C3 grid) then you'd just have the quantity in the first grid and Patrick's suggestion would be sufficient

regards, barry
0

Commented:
Use this formula for C17...

=C3*\$B17

then copy that formula to C17:W24.

Note how I used the \$ to keep an absolute column reference.
0

Author Commented:
Hi Matthew, that's not the sum I need, if you read the sum in say C3, I need a sum the same as this but rather than using the unit price (Column H) it should be using the cost price from the Value worksheet.
0

Commented:
Hello Simon,

I believe Patrick's suggestion does what you asked.......but I would query the logic of what you are doing here. Doesn't the original amount already include the unit price (multiplied by the quantity)?

regards, barry
0

Author Commented:
Hi Barry,

That does not seem to work correctly, it should be timesing the quantity by the cost price in Value worksheet instead of the unit price from sales worksheet.

Regards

Simon
0

Commented:
Isn't that what it does?

When I use that formula with your data I get all zeroes except for C18 and C19 - those two are 20 and 80 respectively because the quantities are 1 and 4, what results did you expect?

barry
0

Author Commented:
I have a spread sheet with 13,000 rows and real data and when I put it in that I have sales of £288 for apr-10 for the 1050 but cost price of zero.
0

Author Commented:
doh, my mistake as I did not increase from 100 to 13,000.  All sorted now that's great cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.