Solved

# Sum required for Excel 2003

Posted on 2011-10-30
Medium Priority
275 Views
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
Question by:Simonrepro
• 4
• 3

LVL 93

Expert Comment

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

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

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

LVL 50

Accepted Solution

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

Author Comment

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

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

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

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

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.
###### Suggested Courses
Course of the Month17 days, 8 hours left to enroll