Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing Columns to each other Sung Excel Discussion (Misc queries) 2 May 25th 07 08:17 PM
Comparing data... NWO Excel Discussion (Misc queries) 2 May 2nd 07 04:34 AM
Comparing An Array in VBA Greg Excel Discussion (Misc queries) 3 January 19th 06 07:31 PM
Comparing two tables Peter Steiner Excel Discussion (Misc queries) 6 December 23rd 05 11:11 AM
Comparing Two Worksheets for changes Jugglertwo Excel Discussion (Misc queries) 1 December 7th 05 09:56 PM


All times are GMT +1. The time now is 10:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"