Count if past today () - 30 days

S

Scott_goddard

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 :(
 
M

Mike H

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

Dianne

Scott_goddard;701223 said:
Hi all,

Trying to count the amount of risks the are open and and past today date by
30 days...

So far i have

=COUNTIF(C9:C98,"Open"'(R2) Risks Log'!C9:C98,">="&TODAY() - 7)) but can
not get this to work :(



Scott,

Try this formula

=COUNTIFS(C9:C98,"open",c9:c98,"<"&TODAY()-30) Replace the ranges a
you need.

Dianne
 
S

Scott_goddard

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

Scott_goddard

This is very similar to what i tried....it only returns "Name"

This can not be that hard....
 
S

Scott_goddard

Mike,

I got it to work...

=SUMPRODUCT(('(R2) Risks Log'!L9:L98="Open")*('(R2) Risks
Log'!C9:C98<>"")*('(R2) Risks Log'!L9:L98>=TODAY()-30))

I think i didnt make it clear that the variables "open" and the "date" were
in different col.....I dont quiet understand how your fourmula works...could
you explain....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top