select this weeks data only

[Vass]

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

[Trevor Shuttleworth]

Vass

I think you need to use SUMPRODUCT rather than COUNTIF

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

Note that in these sort of functions, you must have the same number of rows
in ech case

Regards

Trevor


"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)*(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]

"Trevor Shuttleworth" <[email protected]> wrote in message
news:[email protected]...
> Vass
>
> I think you need to use SUMPRODUCT rather than COUNTIF
>
> =SUMPRODUCT((data!FU$2:FU$6474="salesrep1")*(MONTH(data!M$2:M$6474)=$F$1)*(YEAR(data!M$2:M$6474)=$F$3),data!E$2:E$6474))
>
> Note that in these sort of functions, you must have the same number of
> rows in ech case


I have selected the whole worksheet including empty rows and called it
'data'
so when I refresh the data coming from the accounts package it always
includes
all new data
Do you think this will not work then?
Thanks
--
Vass

[Vass]

"Vass" <[email protected]> wrote in message
news:[email protected]...
{
=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))}

Missed the fact this is in curly brackets
--
Vass

[Trevor Shuttleworth]

SUMPRODUCT, AFAIK, will not work with entire columns.


"Vass" <[email protected]> wrote in message
news:[email protected]...
>
>
> "Trevor Shuttleworth" <[email protected]> wrote in message
> news:[email protected]...
>> Vass
>>
>> I think you need to use SUMPRODUCT rather than COUNTIF
>>
>> =SUMPRODUCT((data!FU$2:FU$6474="salesrep1")*(MONTH(data!M$2:M$6474)=$F$1)*(YEAR(data!M$2:M$6474)=$F$3),data!E$2:E$6474))
>>
>> Note that in these sort of functions, you must have the same number of
>> rows in ech case
>
>
> I have selected the whole worksheet including empty rows and called it
> 'data'
> so when I refresh the data coming from the accounts package it always
> includes
> all new data
> Do you think this will not work then?
> Thanks
> --
> Vass
>