Count if past today () - 30 days
in [Functions]
|
From: Scott_goddard on 16 Apr 2010 11:16 Hi all, Trying to count the amount of risks the are open and and past todays date by 30 days... So far i have =COUNTIF(C9:C98,"Open"'(R2) Risks Log'!C9:C98,">="&TODAY() - 7)) but i can not get this to work :(
From: Mike H on 16 Apr 2010 12:33 Hi, Maybe this =SUMPRODUCT((C9:C98="Open")*('(R2) Risks Log'!C9:C98<>"")*('(R2) Risks Log'!C9:C98<=TODAY()-30)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Scott_goddard" wrote: > Hi all, > > Trying to count the amount of risks the are open and and past todays date by > 30 days... > > So far i have > > =COUNTIF(C9:C98,"Open"'(R2) Risks Log'!C9:C98,">="&TODAY() - 7)) but i can > not get this to work :(
From: Dianne on 16 Apr 2010 13:27 Scott_goddard;701223 Wrote: > Hi all, > > Trying to count the amount of risks the are open and and past todays date by > 30 days... > > So far i have > > =COUNTIF(C9:C98,"Open"'(R2) Risks Log'!C9:C98,">="&TODAY() - 7)) but i can > not get this to work :( Scott, Try this formula =COUNTIFS(C9:C98,"open",c9:c98,"<"&TODAY()-30) Replace the ranges as you need. Dianne -- Dianne ------------------------------------------------------------------------ Dianne's Profile: http://www.thecodecage.com/forumz/member.php?u=1755 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=196189 http://www.thecodecage.com/forumz
From: Scott_goddard on 20 Apr 2010 11:48 Sorry didnt work....I have to chane it slightly as i think i gave you an incorrect version...This just returns "Ref" - any other ideas... =SUMPRODUCT(('(R2) Risks Log'!C9:C98="Open")*('(R2) Risks Log'!C9:C98<>"")*('(R2) Risks Log'!C9:C98<=TODAY()-30)) "Mike H" wrote: > Hi, > > Maybe this > > =SUMPRODUCT((C9:C98="Open")*('(R2) Risks Log'!C9:C98<>"")*('(R2) Risks > Log'!C9:C98<=TODAY()-30)) > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Scott_goddard" wrote: > > > Hi all, > > > > Trying to count the amount of risks the are open and and past todays date by > > 30 days... > > > > So far i have > > > > =COUNTIF(C9:C98,"Open"'(R2) Risks Log'!C9:C98,">="&TODAY() - 7)) but i can > > not get this to work :(
From: Scott_goddard on 20 Apr 2010 11:51
This is very similar to what i tried....it only returns "Name" This can not be that hard.... "Dianne" wrote: > > Scott_goddard;701223 Wrote: > > > Hi all, > > > > Trying to count the amount of risks the are open and and past todays > date by > > 30 days... > > > > So far i have > > > > =COUNTIF(C9:C98,"Open"'(R2) Risks Log'!C9:C98,">="&TODAY() - 7)) but i > can > > not get this to work :( > > > > Scott, > > Try this formula > > =COUNTIFS(C9:C98,"open",c9:c98,"<"&TODAY()-30) Replace the ranges as > you need. > > Dianne > > > -- > Dianne > ------------------------------------------------------------------------ > Dianne's Profile: http://www.thecodecage.com/forumz/member.php?u=1755 > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=196189 > > http://www.thecodecage.com/forumz > > . > |