using AND/BETWEEN in a countif formula

C

creed

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
 
B

Bondi

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:B50000>TODAY()-6))

Regards,
Bondi
 
M

Max

Try:
=SUMPRODUCT((TEXT('Input Page'!A2:A50000,"dddd")="Monday")*('Input
Page'!A2:A50000>=TODAY()-6)*('Input Page'!A2:A50000<=TODAY()))
 
M

Max

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

creed

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
 
B

Bob Phillips

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

Max

creed said:
what is the purpose of the TEXT and dddd?
Click to expand...

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
Click to expand...

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

daddylonglegs

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

Max

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

creed

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

Max

creed said:
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.
Click to expand...

ah, glad that one got cleared up !
is there a way to include the entire column in the formula instead of
using a2:a50000?
Click to expand...

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

daddylonglegs

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

=COUNTIF('Input Page'!B2:B65536,"="&TODAY()-WEEKDAY(TODAY(),3)
 
Top