using AND/BETWEEN in a countif formula
 

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

using AND/BETWEEN in a countif formula
 

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

using AND/BETWEEN in a countif formula
 

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

using AND/BETWEEN in a countif formula
 

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
---

using AND/BETWEEN in a countif formula
 

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

using AND/BETWEEN in a countif formula
 

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

using AND/BETWEEN in a countif formula
 

"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
---

using AND/BETWEEN in a countif formula
 

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

using AND/BETWEEN in a countif formula
 

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
---

using AND/BETWEEN in a countif formula
 

thanks for that!

i guess i said the wrong thing in my first post...i did mean that
column b is the actual date, and column a uses a forumla to turn it
into the weekday.

is there a way to include the entire column in the forumla instead of
using a2:a50000?


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

using AND/BETWEEN in a countif formula
 

"creed" wrote:
thanks for that!
i guess i said the wrong thing in my first post...i did mean that
column b is the actual date, and column a uses a formula to turn it
into the weekday.

ah, glad that one got cleared up !

is there a way to include the entire column in the formula instead of
using a2:a50000?

well, we can't use entire col references (eg: A:A, B:B) with SUMPRODUCT but
you could go up to A2:A65536 which is the entire col bar the 1st row. But
unless you really have / expect to have data going right down there, it's
best to use the smallest possible range sufficient to cover the max expected
extent of data for optimal calc performance (speed of calcs). Even the
original A2:A50000 is already quite large, imo <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

using AND/BETWEEN in a countif formula
 

As long as you only have dates in column B (not dates with times) still
simpler as far as I can see, to use

=COUNTIF('Input Page'!B2:B65536,"="&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