Formula >=90 days from today based on 2 other coniditions in a separate column

D

distillerjunkie

New member
Joined
Mar 16, 2016
Messages
33
Reaction score
0
Points
0
I need a formula to count cells in Columb B that are greater than or equal to 90 days from today based on another column's information. Column B has dates. Column F has either the word "Investigating" or "Implementing." Column F also has the word "Closed" but I only want to count cells in column B that are 90 days or older from today if column F is either "Investigating" or "Implementing."

I have this formula that returns 0: =COUNTIFS('HIT Cards 2016'!B:B,"NOW()-90",'HIT Cards 2016'!G:G,"Investigating",'HIT Cards 2016'!G:G,"Implementing")

I have spend months trying to figure this out. Please help! :mmph:
 
Perhaps =sum(COUNTIFS('HIT Cards 2016'!B:B,">="&(TODAY()-90),'HIT Cards 2016'!G:G,{"Investigating","Implementing"}))

Entered as an array with Ctrl+Shift+Enter

(The NOW() function gives date and actual time)
 
Array?

How do I enter as an array? This returned FALSE.





Pecoflyer said:
Perhaps =sum(COUNTIFS('HIT Cards 2016'!B:B,">="&(TODAY()-90),'HIT Cards 2016'!G:G,{"Investigating","Implementing"}))

Entered as an array with Ctrl+Shift+Enter

(The NOW() function gives date and actual time)
Click to expand...
 
attachment.php
Pecoflyer said:
Please post a sample sheet
Click to expand...
 

Attachments

  • HIT.PNG
    HIT.PNG
    54.5 KB · Views: 62
I think you misunderstood. An image cannot be worked with. Please post a sheet. Thx
 
In your post #1 you wrote ....'HIT Cards 2016'!G:G,"Investigating",'HIT Cards 2016'!G:G,"Implementing"

Col G does not contain neither of these strings, so result is 0.

In fact it's col F that contains these strings. You should adapt your formula accordingly


BTW working on protected sheets is not easy :lock1:
 
Wrong?

View attachment HIT_2016.xlsm

I'm sorry about the protections. They are a necessity in my workplace. I must have forgotten to take them off when I uploaded the sheet.

The result came back 41 but should have come back as 9? I fixed the column issue. That happened because there was a hidden column that I had deleted and forgot to update the formula. Thanks for pointing that out. At least the formula is calculating something but not what I need.

I just want it to count Dates in column B that are 90 days or more overdue for only Implementing and Investing status in Column F, not Closed status.


Pecoflyer said:
In your post #1 you wrote ....'HIT Cards 2016'!G:G,"Investigating",'HIT Cards 2016'!G:G,"Implementing"

Col G does not contain neither of these strings, so result is 0.

In fact it's col F that contains these strings. You should adapt your formula accordingly


BTW working on protected sheets is not easy :lock1:
Click to expand...
 
English not being my mother tongue I don't understand exactly what you mean by "90 days overdue". Does that mean the date in col B to be considered for counting is between today()-90 and TODAY() ,in this case I count 41

Also be aware that B 52 contains a text string which is always larger than any number and thus also counted
 
90 days?

What I mean is Today ()<=90 or 90 days less than today. If today is 3/18/16, 90 days less would be 12/19/15.



Pecoflyer said:
English not being my mother tongue I don't understand exactly what you mean by "90 days overdue". Does that mean the date in col B to be considered for counting is between today()-90 and TODAY() ,in this case I count 41

Also be aware that B 52 contains a text string which is always larger than any number and thus also counted
Click to expand...
 
So, as I already suggested, change the date test part to $B2<=(TODAY()-90);OR($F2="investigating";$F2="implementing"))

BTW please do not quote entire posts. They clutter the thread and make it hard to read
 
Last edited:
=$B2<=(TODAY()-90);OR($F2="investigating";$F2="implementing")) as formula for Conditional Formatting
 
You must log in or register to reply here.
Back
Top