Compare two columns, text to date.

I have two columns, salesman and date. This workbook tracks quotes by salesman. I need to know how many quotes for each salesman by the month. So what I need to know is how quotes salesman 'a' had in January and February and so on. I have several worksheets in this workbook that are all formatted the same. If anybody and help I would really appreciate it.

Probably Pivot Table is Best for this:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

If you can actually list all the sales people in a table vertically, and the months horizontally, then you can also use a formula to get the infor into this table...

e.g. =Sumproduct(--($A$1:$A$100=$X2),--(Text($B$1:$B$100,"MMMM")=Y$1))

where A1:A100 contains your salespeople list and B1:B100 the dates...

X2 downwards contains the unique salespeople's names and Y1 rightwards contains the Months listed as January, February, etc..

Formula would go in Y2 and copied down and across the table.

Do you have a list of all the salespeople in a summary sheet?

If yes, can you post a sample workbook with dummy data and use the sheetnames you are actually using....

Here....

click Post Reply and then click the paperclip icon.. Browse and Upload.

A couple of alternatives:

Note: First take the dot away from Jan. and Feb. in C33 and D33...

In Summary!C34:

=SUMPRODUCT(--(Active!$D$4:$D$200=$B34),--(TEXT(Active!$C$4:$C$200,"mmm")=C$33))+SUMPRODUCT(--(Cold!$D$4:$D$200=$B34),--(TEXT(Cold!$C$4:$C$200,"mmm")=C$33))+SUMPRODUCT(--(Wins!$D$4:$D$200=$B34),--(TEXT(Wins!$C$4:$C$200,"mmm")=C$33))+SUMPRODUCT(--(Sold!$D$4:$D$200=$B34),--(TEXT(Sold!$C$4:$C$200,"mmm")=C$33))+SUMPRODUCT(--(Lost!$D$4:$D$200=$B34),--(TEXT(Lost!$C$4:$C$200,"mmm")=C$33))+SUMPRODUCT(--(Dead!$D$4:$D$200=$B34),--(TEXT(Dead!$C$4:$C$200,"mmm")=C$33))

copied down the table and across the columns

A shorter version uses a free Addin function, downloaded from here: Morefunc.xll

=SUMPRODUCT(--(THREED(Active:Dead!$D$4:$D$200)=$B34),--(TEXT(THREED(Active:Dead!$C$4:$C$200),"mmm")=C$33))

My privilege.. just like doing this stuff.. so the challenge and satisfaction that I helped you solve the problem was enough

Please though, mark the thread as "Solved" (click faq in my signature for instruction) and perhaps also have a look at the orange line in my signature....