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
SimonreproAsked:
Who is Participating?
 
barry houdiniCommented:
Hello Simon,

Sorry I posted my reply before I saw your reply - that answers my query to some extent

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
 
Patrick MatthewsCommented:
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
 
SimonreproAuthor 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
barry houdiniCommented:
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
 
SimonreproAuthor 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
 
barry houdiniCommented:
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
 
SimonreproAuthor 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
 
SimonreproAuthor 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.

All Courses

From novice to tech pro — start learning today.