Excel/excel formula


QUESTION: Running 2011 Excel for Mac version 14.0.0 Trying to set up a spreadsheet to track sales by customer and month with the year's total changing with each daily entry added to the previous Monthly amount.

Customer       Monthly Sales       2012 Total

Each time I enter an amount under month I would like the new total reflected in the Total.
When I set up a new column for the next month I want those amounts added to the yearly total.
I would like to sort the entire spreadsheet to show the annual total (highest to lowest) with the corresponding name and monthly total in the same row. Can you help? (Am I clear)

ANSWER: Can't really imagine your layout from the information given.

I would enter Customer name in column A, Sales in column B, Date in column C.

With that, you can create a table off to the right that provides the SUM for each customer using SUMIF() formulas.


---------- FOLLOW-UP ----------

QUESTION: This is my layout
        A          B          C          D          E
Customer        October sales        November sales         December Sales          Total 2012 Sales
   ABC          100          100          200
   DEF          200          0          100          300
   GHI          175          0          0          175
As I add sales to the monthly total during the month I would like the yearly total to change. When I start the next month I would like it added to the yearly total.  I would also like to be able to sort the 2012 sales (E) column with the highest to the lowest and have all the other columns sort so that the customer name corresponds to the amount in BCDE (for printing). I hope this is a little clearer

ANSWER: As long as you're going to lay that out fully in advance, then the formula in E2 is a simple:


---------- FOLLOW-UP ----------

QUESTION: Thank you, perfect. Now the second part of the question. I want to sort the 2012 sales total (E) showing highest to lowest.
When I do that, column A (customer name) does not change to correspond to the info in E. I need to have all the columns change. There must be a way. Thanks

Make sure you highlight columns A:E, then run the sort using column E as the primary key.  Only the columns highlighted are included in the sort.

I'm surprised Excel doesn't warn you, mine does.  If I tried to sort column E only Excel asks me if I want to expand to include the entire data set.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2017 About.com. All rights reserved.