Need to count the amount of entries by month for sales person

[avwolf]

I have to find the sum of entries by month for multiple sales people (over 9000 entries).

I have a date column that reflects entry dates and another column with salesperson's name along with other information but these are the sorting criteria that I want to get the results for.


EXAMPLE DATA


Entry Date Sales Person

Oct 1, 2011 AZ

Oct 2, 2011 AZ

Nov 4, 2011 AZ

Nov 4, 2011 BB

Nov 4, 2011 HN


Results desired (but I don't know how).

Amt of entries per sales person by month

MONTH/YEAR AZ BB HN

OCT, 2011 2 0 0

NOV, 2011 1 1 1

 

[fred]

First of all, change all the date to first of the month.


Then use pivot table.

 

[Robapottamus]

Hi There


An alternative suggestion so you don't have to change your current data is to insert a new column with the formula =month(Cell reference), linking to your date column. This would mean that you can keep accurate records of which day the sale occured while still summarising by month.


Then you can Pivot the data by sales person for the summary.

 

[Robapottamus]

In addition to my post above, if your data runs across multiple years, then you can use the formula =year(A1)&" "&month(A1) to identify the year also. Could use this instead of the month formula suggested above.

 

[NARAYANK991]

Hi ,


Assume your data is in cells A5 through B10 as follows ( I have just copied from your post ) :


Entry Date -- Sales Person

1-Oct-11 ------ AZ

2-Oct-11 ------ AZ

4-Nov-11 ------ AZ

4-Nov-11 ------ BB

4-Nov-11 ------ HN


Create two dynamic named ranges as follows :


1. Date_Range referring to : =OFFSET(Sheet2!$A$6,0,0,COUNTA(Sheet2!$A:$A)-1)


2. Sales_Persons_List : =OFFSET(Sheet2!$B$6,0,0,COUNTA(Sheet2!$B:$B)-1)


Now , create a table in some unused part of your worksheet as follows :


Month ----- AZ --- BB --- HN

Oct-11

Nov-11


Say this table is from H5 through K7.


In cell I6 , put in the following formula :


=SUMPRODUCT(--(MONTH(Date_Range)=MONTH($H6))*--(Sales_Persons_List=I$5))


Copy this formula to the other cells J6 , K6 , I7 , J7 and K7.


You should get your result.


Narayan

 

[avwolf]

Thank you all for the input. I went with adding a column and =Month(CELL) then Pivot. Worked great.