?
Solved

Sum required for Excel 2003

Posted on 2011-10-30
8
Medium Priority
?
275 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Simonrepro
  • 4
  • 3
8 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37053265
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 Comment

by:Simonrepro
ID: 37053272
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 37053280
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 37053297
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
 

Author Comment

by:Simonrepro
ID: 37053310
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 37053333
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 Comment

by:Simonrepro
ID: 37053344
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 Closing Comment

by:Simonrepro
ID: 37053365
doh, my mistake as I did not increase from 100 to 13,000.  All sorted now that's great cheers!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question