sum of weeks sales please

[Vass]

further to assistance from Bob
i.e.
=SUMPRODUCT(--(data!$FU$2:$FU$6505<>"MAREK"),--(data!$M$2:$M$6505>=TODAY()-WEEKDAY(TODAY())+2),--(data!$M$2:$M$6505<TODAY()-WEEKDAY(TODAY())+2+7))

this doesn't seem to work
If I input the date as 13/03/2006 (monday next week) I want the sales to
revert to zero and start building again
but the formula above shows a result so must be erading from previous week.

Can anyone help ?

ta

 

[Bob Phillips]

Change TODYA() to --"2006-03-13"

or put that date in a cell and refer to that.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Vass]

Change TODYA() to --"2006-03-13"

or put that date in a cell and refer to that.

thank Bob, but this is a working report
I don't want user interaction, just a report on the front screen of ongoing
sales day by day, week by week
To expect the sales muppets to change the date every day, they might as well
re-sum the data too.
Is there no way to do this ?
thanks

 

[Bob Phillips]

Vass,

I am not understanding now. I gave you a formula based upon this week. I
presume you now want to be able to create a report based upon some specified
week. How do you determine what that specified week is?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Vass]

Vass,

I am not understanding now. I gave you a formula based upon this week. I
presume you now want to be able to create a report based upon some
specified
week. How do you determine what that specified week is?

hi Bob thanks for helping me

OK
I set the current date in a cell with Today()
I want excel to look at todays date and determine if its a tuesday,
wednesday etc
then pull the sales from Monday to that day (If friday then 5 days of sales,
if monday then no sales till they start coming in)
Only the current week to appear
take a look at my worksheet from here and see what I'm up to please
http://www.naldernet.plus.com/holding/LiveSales.xls
If today was a monday at 9am i'd expect the sales to be zero
If today was wednesday lunchtime, id expect sales to be 2 and a half days
worth
If today was FRiday at 5:30 Id expect the sales to be a full weeks worth
then, come in monday morning and all sales are zero again
does that make sense?

I presume I need to tell excel, mon,tues,wed,thurs,fri all count in one
week?
thanks again for your time on this one

 

[Bob Phillips]

Vass,

There was no data for me to verify on, but I think this is what you now want

=SUMPRODUCT(--(data!$FU$2:$FU$65<>"MAREK"),--(data!$M$2:$M$65>=E2-WEEKDAY(E2
)+2),--(data!$M$2:$M$65<=E2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Vass]

Vass,

There was no data for me to verify on, but I think this is what you now
want

=SUMPRODUCT(--(data!$FU$2:$FU$65<>"MAREK"),--(data!$M$2:$M$65>=E2-WEEKDAY(E2
)+2),--(data!$M$2:$M$65<=E2))

Im sure we are almost there bob

this is pulling the Reps name from data!FU, and the Dates from Data!M
matching to TODAY() that sitting in E2
but I'm missing the sales value sitting in data!I which is what I want
totalling.
am i missing something?
sorry the data was missing, I didnt want to put our sales into the public
domain.

 

[Vass]

S'ok think I've got it

add this to the end of the fomula?

,data!$I$2:$I$6505)

ta muchly

 

[Bob Phillips]

If there had been data there, I might have been able to figure that <bg>

At least the workbook gave me better insight to what you were trying to do
<ebg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)