ok...my current formula is *=COUNTIF('Input Page'!A2:A50000,"=Monday")*

i'd like to change it to check what day is in the field and then only
do the above formula if that day is within the past week.

so i need the *"=Monday"* section to be changed to read "(is equal to
monday) and (is between today and today-6)

i'm having trouble getting this to work...it's probably so easy that
i'm just over looking it...any help is much appreciated


--
creed
------------------------------------------------------------------------
creed's Profile: http://www.excelforum.com/member.php...o&userid=34424
View this thread: http://www.excelforum.com/showthread...hreadid=546146

Hi,

Maybe you can use something like this if you have the dates in columb B
next to your days.

=SUMPRODUCT(--('Input Page'!A2:A50000,="Monday"),--('Input
Page'!B2:B50000TODAY()-6))

Regards,
Bondi

Try:
=SUMPRODUCT((TEXT('Input Page'!A2:A50000,"dddd")="Monday")*('Input
Page'!A2:A50000=TODAY()-6)*('Input Page'!A2:A50000<=TODAY()))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"creed" wrote:

ok...my current formula is *=COUNTIF('Input Page'!A2:A50000,"=Monday")*

i'd like to change it to check what day is in the field and then only
do the above formula if that day is within the past week.

so i need the *"=Monday"* section to be changed to read "(is equal to
monday) and (is between today and today-6)

i'm having trouble getting this to work...it's probably so easy that
i'm just over looking it...any help is much appreciated


--
creed
------------------------------------------------------------------------
creed's Profile: http://www.excelforum.com/member.php...o&userid=34424
View this thread: http://www.excelforum.com/showthread...hreadid=546146

The preceding assumes you have real dates populating A2:A50000 fully

If there's the possibility of empty cells within A2:A50000,
perhaps safer to include another conditional check
within the SUMPRODUCT multiplication: ('Input Page'!A2:A50000<"")

viz. try this:
=SUMPRODUCT((TEXT('Input Page'!A2:A50000,"dddd")="Monday")*('Input
Page'!A2:A50000=TODAY()-6)*('Input Page'!A2:A50000<=TODAY())*('Input
Page'!A2:A50000<""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

what is the purpose of the TEXT and dddd?


i'm using =SUMPRODUCT((TEXT('Input
Page'!A2:A50000,"dddd")="Monday")*('Input
Page'!B2:B50000=TODAY()-6)*('Input Page'!B2:B50000<=TODAY())*('Input
Page'!B2:B50000<""))

and getting a #REF! error


--
creed
------------------------------------------------------------------------
creed's Profile: http://www.excelforum.com/member.php...o&userid=34424
View this thread: http://www.excelforum.com/showthread...hreadid=546146

That is to transform the dates into a day of the week string, which is then
compared to be Monday.

Why did you change the A range to a B range. The formula Max gave assumed
that the dates were in A2:A5000 and it was those that were to be tested for
Monday, and within the past week. It could have been

=SUMPRODUCT(--('Input Page'!A2:A50000=2),
--('Input Page'!A2:A50000=TODAY()-6),
--('Input Page'!A2:A50000<=TODAY()),
--('Input Page'!A2:A50000<""))

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"creed" wrote in
message ...

what is the purpose of the TEXT and dddd?


i'm using =SUMPRODUCT((TEXT('Input
Page'!A2:A50000,"dddd")="Monday")*('Input
Page'!B2:B50000=TODAY()-6)*('Input Page'!B2:B50000<=TODAY())*('Input
Page'!B2:B50000<""))

and getting a #REF! error


--
creed
------------------------------------------------------------------------
creed's Profile:
http://www.excelforum.com/member.php...o&userid=34424
View this thread: http://www.excelforum.com/showthread...hreadid=546146

"creed" wrote:
what is the purpose of the TEXT and dddd?

It was clarified earlier:
The preceding assumes you have real dates populating A2:A50000 fully
The TEXT function converts the real dates within A2:A50000 to "days of the
week" text in "dddd" format, eg: Monday, Tuesday, ... This is for the purpose
of picking out the Monday's within the range

i'm using =SUMPRODUCT((TEXT('Input
Page'!A2:A50000,"dddd")="Monday")*('Input
Page'!B2:B50000=TODAY()-6)*('Input Page'!B2:B50000<=TODAY())*('Input
Page'!B2:B50000<""))
and getting a #REF! error

Think you were hit by line breaks and/or mis-matches between the sheetnames
in the formula and the actual name showing on the sheet tab. First, ensure
the entire formula is sitting properly within the cell's formula bar, with
all the inadvertent line breaks corrected/removed after you copy-paste direct
from the post into the formula bar. The sheet name should read as just:
Input Page. Check that the name matches exactly* with what you have on the
sheet tab (eg: no extra white spaces, no typos, etc)
*except for case

(Ensure that you also have real dates within B2:B50000)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

If you're just comparing dates in column A against today's date (not
another date in column B) perhaps

=COUNTIF('Input Page'!A2:A50000,"="&TODAY()-WEEKDAY(TODAY(),3))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=546146

Revisiting the thread .. perhaps you could have meant:
you have real dates in B2:B50000,
and in A2:A50000, you have text such as: Monday, Tuesday, etc

then we could try this amendment:
=SUMPRODUCT(('Input Page'!A2:A50000="Monday")*('Input
Page'!B2:B50000=TODAY()-6)*('Input Page'!B2:B50000<=TODAY())*('Input
Page'!B2:B50000<""))

(as before, pl ensure that the inadvertent line breaks are removed/corrected
after you copy n paste the formula from the post into the formula bar)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---