Need help producing simple sales figures

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

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)


"Mark_King" <markking@hotmail.com> wrote in message
news:egUXU4lKFHA.656@TK2MSFTNGP14.phx.gbl...
> 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
>
>

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

Thanks so much for your help!

Mark
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23B8LyHmKFHA.4032@TK2MSFTNGP14.phx.gbl...
> 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)
>
>
> "Mark_King" <markking@hotmail.com> wrote in message
> news:egUXU4lKFHA.656@TK2MSFTNGP14.phx.gbl...
>> 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
>>
>>
>
>

Wow. Thanks, its my pleasure :-)

Bob


"Mark_King" <markking@hotmail.com> wrote in message
news:ucYvMOmKFHA.484@TK2MSFTNGP15.phx.gbl...
> That's absolutly fantastic Bob, that's exactly what I'm looking for and it
> works fantastic.
>
> Thanks so much for your help!
>
> Mark
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23B8LyHmKFHA.4032@TK2MSFTNGP14.phx.gbl...
> > 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)
> >
> >
> > "Mark_King" <markking@hotmail.com> wrote in message
> > news:egUXU4lKFHA.656@TK2MSFTNGP14.phx.gbl...
> >> 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
> >>
> >>
> >
> >
>
>