Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing more than two
I have a spreadsheet that tracks Date, Salesman and Sale Amount in separate
columns with information over a year. How do I do an analysis of monthly sales for each salesman individually. I tried sumif(and but cannot seem to make it work? I am sure there is a way. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing more than two
Use a Pivot Table, that is the easiest way!!!
http://www.contextures.com/xlPivot02.html http://peltiertech.com/Excel/Pivots/pivottables.htm Regards, Ryan--- -- RyGuy "Vern" wrote: I have a spreadsheet that tracks Date, Salesman and Sale Amount in separate columns with information over a year. How do I do an analysis of monthly sales for each salesman individually. I tried sumif(and but cannot seem to make it work? I am sure there is a way. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing more than two
What are you hoping to end up with? A table like this:
1 2 3 4 5 etc Salesman_1 Salesman_2 Salesman_3 and so on? Where the numbers across the top are the months and the salesmen's names are down column A? Will you only have a single year's worth of data in your file, then how would you like the different years to be treated? You can use a single SUMPRODUCT formula in cell B1 to do this, and that can then be copied across and down. The actual details will depend on your data layout and number of records - could you post some more details, please? Hope this helps. Pete "Vern" wrote in message ... I have a spreadsheet that tracks Date, Salesman and Sale Amount in separate columns with information over a year. How do I do an analysis of monthly sales for each salesman individually. I tried sumif(and but cannot seem to make it work? I am sure there is a way. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing more than two
You could use the SUMPRODUCT function. Let's say Date is in Column A, Name
is in Column B, and Amount is in Column C. =SUMPRODUCT(--(MONTH(A1:A100)=9),--(B1:B100="Smith"),C1:C100) This should total all of Smith's sales for the month of September. If your Date column spans multiple years, then you'd need to account for the year as well. HTH Elkar "Vern" wrote: I have a spreadsheet that tracks Date, Salesman and Sale Amount in separate columns with information over a year. How do I do an analysis of monthly sales for each salesman individually. I tried sumif(and but cannot seem to make it work? I am sure there is a way. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing more than two
I use the spreadsheet to keep track of sales for the past year and a half. I
track sales by different type of product, supplier etc. but would like to look at a salesman for each month as we go along. My current spreadsheet is approx 20 columns by 500 rows - each row representing a new sale. Thanks for looking into this. Vern "Pete_UK" wrote: What are you hoping to end up with? A table like this: 1 2 3 4 5 etc Salesman_1 Salesman_2 Salesman_3 and so on? Where the numbers across the top are the months and the salesmen's names are down column A? Will you only have a single year's worth of data in your file, then how would you like the different years to be treated? You can use a single SUMPRODUCT formula in cell B1 to do this, and that can then be copied across and down. The actual details will depend on your data layout and number of records - could you post some more details, please? Hope this helps. Pete "Vern" wrote in message ... I have a spreadsheet that tracks Date, Salesman and Sale Amount in separate columns with information over a year. How do I do an analysis of monthly sales for each salesman individually. I tried sumif(and but cannot seem to make it work? I am sure there is a way. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Columns to each other | Excel Discussion (Misc queries) | |||
Comparing data... | Excel Discussion (Misc queries) | |||
Comparing An Array in VBA | Excel Discussion (Misc queries) | |||
Comparing two tables | Excel Discussion (Misc queries) | |||
Comparing Two Worksheets for changes | Excel Discussion (Misc queries) |