Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Calculate column of sales and expenses

Posted on 2014-02-01
Medium Priority
462 Views
Hi experts!  Attached is a spreadsheet that I need some expert assistance with as I can't figure out an easier way to calculate two columns.

For the horizontal bar at the bottom, the net total for sales should be the (qty x sale price) for each order.  The same for the "cost" column.  I started a formula that looked like this:

Sale Price
Net Total   =(((d3*c3)+(d4*c4))-((e3*c3)+(sum(f3:i3)+sum(f4:i4))

Since some of the spreadsheets have 50-100 orders, the net total for "sales" and "costs" would be incredibly time sensitive to go back and fix previous months (Oct-Feb).  Is there any other way to calculate this besides one manual entry at a time?  I hope so.  Thanks experts!!
EE-Question.xlsx
0
Question by:samiam41
• 2
• 2

LVL 18

Accepted Solution

Steven Harris earned 2000 total points
ID: 39827132
As a quick workaround, add a helper column.

In Column K, use:

=SUM(C3*D3) and then copy down to all rows.

Then use:

Net Sale Price - =SUM(K3:K4)
Net (Profit?) in E15 - =(D14-E14)+SUM(F14:I14)
0

LVL 9

Author Comment

ID: 39827150
ThinkSpaceSolutions, thank you for the quick solution.  I plugged it in and it I like the results.  I hide the columns from the view.  When I print, will the hidden columns stay hidden?  I can't remember if that is a setting or not (don't print hidden column).
0

LVL 9

Author Closing Comment

ID: 39827154
Great suggestion and will be the solution I implement.  Thanks ThinkSpaceSolutions!
0

LVL 18

Expert Comment

ID: 39827160
When I print, will the hidden columns stay hidden?
Sure will.

Great suggestion and will be the solution I implement.
Glad it worked out!  Helper columns are sometimes the easiest to understand, implement and edit, so it has more value than a script that just does everything for you.
0

## Featured Post

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on tâ€¦
###### Suggested Courses
Course of the Month7 days, 12 hours left to enroll