Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
countif formula | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
COUNTIF formula problems | Excel Discussion (Misc queries) | |||
Countif formula with multiple criteria ie >30 and <60? | Excel Worksheet Functions |