Calculate sales in the week by day ?

I have a worksheet thats created from a data import from an accounts package
I'd like to sum the values of sales for this week only starting Monday
and the SUM result would grow to Friday before returning to zero next monday
morning
the worksheet has multiple entries for each day, in date format dd/mm/yyyy
at the moment.

I'm managing to count number of sales for the month per sales rep, with the
formula below
but seem to struggle for current week only reporting, (plus I need SUM of
Sales as well as the COUNT)
{=COUNT(IF((data!FU$2:FU$6474="salesrep1")*(MONTH(data!M$2:M$6473)=$F$1)*(YEAR(data!M$2:M$6451)=$F$3),data!E$2:E$6473))}

Fixed items - F1 has the month number and F3 has the year, F2 has the
current Day, E2 has the current date.
Any assistance appreciated
Thanks

--
Vass


--
Vass
................................................
Now: (currently shopping)
Then: A100, MBX80, XL125, CB400, FZR600, CBR600Fv, CBR1100xx-x, YZF-R1

Your formula should wotk with SUM instead of COUNT, but this also should
work

=SUMPRODUCT(--(data!FU$2:FU$6474="salesrep1"),--(MONTH(data!M$2:M$6473)=$F$1
),--(YEAR(data!M$2:M$6451)=$F$3),data!E$2:E$6473))}


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Vass" <[email protected]> wrote in message
news:[email protected]...
> I have a worksheet thats created from a data import from an accounts
package
> I'd like to sum the values of sales for this week only starting Monday
> and the SUM result would grow to Friday before returning to zero next
monday
> morning
> the worksheet has multiple entries for each day, in date format dd/mm/yyyy
> at the moment.
>
> I'm managing to count number of sales for the month per sales rep, with
the
> formula below
> but seem to struggle for current week only reporting, (plus I need SUM of
> Sales as well as the COUNT)
>
{=COUNT(IF((data!FU$2:FU$6474="salesrep1")*(MONTH(data!M$2:M$6473)=$F$1)*(YE
AR(data!M$2:M$6451)=$F$3),data!E$2:E$6473))}
>
> Fixed items - F1 has the month number and F3 has the year, F2 has the
> current Day, E2 has the current date.
> Any assistance appreciated
> Thanks
>
> --
> Vass
>
>
> --
> Vass
> ...............................................
> Now: (currently shopping)
> Then: A100, MBX80, XL125, CB400, FZR600, CBR600Fv, CBR1100xx-x, YZF-R1
>
>

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Your formula should wotk with SUM instead of COUNT, but this also should
> work
>
> =SUMPRODUCT(--(data!FU$2:FU$6474="salesrep1"),--(MONTH(data!M$2:M$6473)=$F$1
> ),--(YEAR(data!M$2:M$6451)=$F$3),data!E$2:E$6473))}
>
>

this will do the weeks sales then Bob?

thanks
--
Vass

Sorry, missed that bit

=SUMPRODUCT(--(Data!F$2:F$6474="salesrep1"),--(Data!M$2:M$6474>=TODAY()-WEEK
DAY(TODAY())+2),
--(Data!M$2:M$6474<TODAY()-WEEKDAY(TODAY())+2+7))

and

=SUMPRODUCT(--(Data!F$2:F$6474="salesrep1"),--(Data!M$2:M$6474>=TODAY()-WEEK
DAY(TODAY())+2),
--(Data!M$2:M$6474<TODAY()-WEEKDAY(TODAY())+2+7),Data!E$2:E$6474)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Vass" <[email protected]> wrote in message
news:[email protected]...
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Your formula should wotk with SUM instead of COUNT, but this also should
> > work
> >
> >
=SUMPRODUCT(--(data!FU$2:FU$6474="salesrep1"),--(MONTH(data!M$2:M$6473)=$F$1
> > ),--(YEAR(data!M$2:M$6451)=$F$3),data!E$2:E$6473))}
> >
> >
>
> this will do the weeks sales then Bob?
>
> thanks
> --
> Vass
>
>

"Bob Phillips" <[email protected]> wrote in message
news:efxD%[email protected]...
> Sorry, missed that bit

Many thanks
--
Vass

"Bob Phillips" <[email protected]> wrote in message
news:efxD%[email protected]...
> Sorry, missed that bit

Many thanks
--
Vass

>
> "Bob Phillips" <[email protected]> wrote in message
> news:efxD%[email protected]...
>> Sorry, missed that bit
>

Bob, it doesn't like looking at my date and year at F1 and F3
I'm using Month(E2) pulling from a cell with +today() in it

so the cells F1 and F3 have '3' and '2006' in them

any ideas ?
thanks
--
Vass

"Vass" <[email protected]> wrote in message
news:[email protected]...
>> "Bob Phillips" <[email protected]> wrote in message
>> news:efxD%[email protected]...
>>> Sorry, missed that bit
>>
>
> Bob, it doesn't like looking at my date and year at F1 and F3
> I'm using Month(E2) pulling from a cell with +today() in it
>
> so the cells F1 and F3 have '3' and '2006' in them
>
> any ideas ?

Scrap that, its working fine now, thanks Bob
--
Vass