Need help producing simple sales figures

M

Mark_King

Hi all - hope somebody can help with this dilema!

I have a worksheet and wondered if anybody could help with this problem:

Column A:A contains a date (within 2005).
Next to each date in column B:B contains a persons name either Bob, Dave or
Andy, again throughout the entire column.

These 2 columns represent who got a sale on a particular date, I want a
formula that will add up the number of sales for each person for each month.

For the next 12 columns (for each of the 12 months) I want each column to
tell me the total number of Bob's there are for January, how many Dave's
there are in January, and how many Andy's there are in January, and so on
for the rest of the columns for the remaining months.

Is there are formula that could calculate the number of times a persons name
appears within a certain month.

I would like to avoid using the 'Sort...' function from the 'Tools' Menu if
possible.


Does anybody have any ideas on how this can be done.

Many thanks for your help in advance.
Mark
 
B

Bob Phillips

Assuming C1 holds a date, like 01-Jan-2005 then in C2

=SUMPRODUCT(--(MONTH($A$2:$A$1000)=MONTH(C$2)),--($B$2:$B$1000="Bob"))

etc down C. YOu can copy acroos the columns.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Mark_King

That's absolutly fantastic Bob, that's exactly what I'm looking for and it
works fantastic.

Thanks so much for your help!

Mark
 
B

Bob Phillips

Wow. Thanks, its my pleasure :)

Bob


Mark_King said:
That's absolutly fantastic Bob, that's exactly what I'm looking for and it
works fantastic.

Thanks so much for your help!

Mark
Click to expand...
 
Top