I have a spreadsheet (daily sales transactions) that contains three columns.
Column A is Sales Rep; Column B is date; Column C is sales.



Data consist of data from the beginning of the year ( say Jan1 2007) thru
today.



I would like to create a summary report in the following format:



Sales Rep Month Sales YTD Sales



I know I can use the sumif to get the year to date sales. What formula can I
use to get the month to date sales as I would need from say June 1 thru June
28?



Your help is greatly appreciated.

=Sumproduct(--(Sheet1!a2:a100=a2),--(month(Sheet1!b2:b100)=6),Sheet1!C2:c100)

Sheet1 contains your transactions

A2 is sales rep on summary

6 is month six (june)

HTH


"Naraine Ramkirath" wrote:

> I have a spreadsheet (daily sales transactions) that contains three columns.
> Column A is Sales Rep; Column B is date; Column C is sales.
>
>
>
> Data consist of data from the beginning of the year ( say Jan1 2007) thru
> today.
>
>
>
> I would like to create a summary report in the following format:
>
>
>
> Sales Rep Month Sales YTD Sales
>
>
>
> I know I can use the sumif to get the year to date sales. What formula can I
> use to get the month to date sales as I would need from say June 1 thru June
> 28?
>
>
>
> Your help is greatly appreciated.
>
>
>

Toppers,

that works great, but can I use this instead?

SUMPRODUCT(--(Sales!$A:$A=Report!B9),--(MONTH(Sales!$F:$F)=6),Sales!$D:$D)

i don't want to have to change the range each day.



"Toppers" <(E-Mail Removed)> wrote in message
news:60A8E8EE-0954-44C0-B7D9-(E-Mail Removed)...
>
=Sumproduct(--(Sheet1!a2:a100=a2),--(month(Sheet1!b2:b100)=6),Sheet1!C2:c100
)
>
> Sheet1 contains your transactions
>
> A2 is sales rep on summary
>
> 6 is month six (june)
>
> HTH
>
>
> "Naraine Ramkirath" wrote:
>
> > I have a spreadsheet (daily sales transactions) that contains three
columns.
> > Column A is Sales Rep; Column B is date; Column C is sales.
> >
> >
> >
> > Data consist of data from the beginning of the year ( say Jan1 2007)
thru
> > today.
> >
> >
> >
> > I would like to create a summary report in the following format:
> >
> >
> >
> > Sales Rep Month Sales YTD Sales
> >
> >
> >
> > I know I can use the sumif to get the year to date sales. What formula
can I
> > use to get the month to date sales as I would need from say June 1 thru
June
> > 28?
> >
> >
> >
> > Your help is greatly appreciated.
> >
> >
> >

No, not unless you use Excel 2007, why don't you just use something that you
will never reach like A1:A25000


--
Regards,

Peo Sjoblom



"Naraine Ramkirath" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Toppers,
>
> that works great, but can I use this instead?
>
> SUMPRODUCT(--(Sales!$A:$A=Report!B9),--(MONTH(Sales!$F:$F)=6),Sales!$D:$D)
>
> i don't want to have to change the range each day.
>
>
>
> "Toppers" <(E-Mail Removed)> wrote in message
> news:60A8E8EE-0954-44C0-B7D9-(E-Mail Removed)...
>>
> =Sumproduct(--(Sheet1!a2:a100=a2),--(month(Sheet1!b2:b100)=6),Sheet1!C2:c100
> )
>>
>> Sheet1 contains your transactions
>>
>> A2 is sales rep on summary
>>
>> 6 is month six (june)
>>
>> HTH
>>
>>
>> "Naraine Ramkirath" wrote:
>>
>> > I have a spreadsheet (daily sales transactions) that contains three
> columns.
>> > Column A is Sales Rep; Column B is date; Column C is sales.
>> >
>> >
>> >
>> > Data consist of data from the beginning of the year ( say Jan1 2007)
> thru
>> > today.
>> >
>> >
>> >
>> > I would like to create a summary report in the following format:
>> >
>> >
>> >
>> > Sales Rep Month Sales YTD Sales
>> >
>> >
>> >
>> > I know I can use the sumif to get the year to date sales. What formula
> can I
>> > use to get the month to date sales as I would need from say June 1 thru
> June
>> > 28?
>> >
>> >
>> >
>> > Your help is greatly appreciated.
>> >
>> >
>> >
>
>

Peo, thanks. that works.
"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:uQFW1$(E-Mail Removed)...
> No, not unless you use Excel 2007, why don't you just use something that
you
> will never reach like A1:A25000
>
>
> --
> Regards,
>
> Peo Sjoblom
>
>
>
> "Naraine Ramkirath" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Toppers,
> >
> > that works great, but can I use this instead?
> >
> >
SUMPRODUCT(--(Sales!$A:$A=Report!B9),--(MONTH(Sales!$F:$F)=6),Sales!$D:$D)
> >
> > i don't want to have to change the range each day.
> >
> >
> >
> > "Toppers" <(E-Mail Removed)> wrote in message
> > news:60A8E8EE-0954-44C0-B7D9-(E-Mail Removed)...
> >>
> >
=Sumproduct(--(Sheet1!a2:a100=a2),--(month(Sheet1!b2:b100)=6),Sheet1!C2:c100
> > )
> >>
> >> Sheet1 contains your transactions
> >>
> >> A2 is sales rep on summary
> >>
> >> 6 is month six (june)
> >>
> >> HTH
> >>
> >>
> >> "Naraine Ramkirath" wrote:
> >>
> >> > I have a spreadsheet (daily sales transactions) that contains three
> > columns.
> >> > Column A is Sales Rep; Column B is date; Column C is sales.
> >> >
> >> >
> >> >
> >> > Data consist of data from the beginning of the year ( say Jan1 2007)
> > thru
> >> > today.
> >> >
> >> >
> >> >
> >> > I would like to create a summary report in the following format:
> >> >
> >> >
> >> >
> >> > Sales Rep Month Sales YTD Sales
> >> >
> >> >
> >> >
> >> > I know I can use the sumif to get the year to date sales. What
formula
> > can I
> >> > use to get the month to date sales as I would need from say June 1
thru
> > June
> >> > 28?
> >> >
> >> >
> >> >
> >> > Your help is greatly appreciated.
> >> >
> >> >
> >> >
> >
> >
>
>