Count if past today () - 30 days

[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

 

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

 

[Dianne]

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

 

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

 

[Scott_goddard]

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

This can not be that hard....

 

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