Need formula for project that are over due

[BB4523]

Hello all,

Trying to figure this by myself and getting more and more confused/frustrated.
Need to figure:
1) Overdue for column "e" only
2) Automatically turn over due dates red

Column H can be deleted just trying to figure this out. Frustrated and need HELP. The green boxes are some of the formulas I was trying to use.


Thank you in advance for your help.

[protonLeah]

Overdue if and only if column G is blank and date today is greater than column F.

Please Login or Register  to view this content.

[BB4523]

Thank you for the help. Couple of questions; 1) how to I change 19f from false to a number? 2) How would I count just the "e" in the e column? I appreciate the quick response!

[protonLeah]

For F19 **:

Please Login or Register  to view this content.

For count of "e", in F18 the number is already correct..
However, for count of upcoming, F20, it should be:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts braces {} around the formula (though do not attempt to manually insert these yourself).

[BB4523]

Oh my gosh, this is awesome! Thank you so much!!!

[BB4523]

One last question about this. I just realized I can have an overdue without having a completed date. How would I add today into the formula, another if formula? I appreciate all of the help and guidance.

Also the formula doesn't work now {=COUNTIFS(E3:E14,"e",F3:F14,"<="&G3:G14)} not sure what happened or what I've done, but I'm sure it was something I have done. I have attached my spreadsheet.

[BB4523]

Can someone please explain why my formula doesn't work?

[BB4523]

Please????????

[GeoffW283]

Probably just me, but I'm not understanding how the original F19 formula worked. Try the following in F19:

Please Login or Register  to view this content.

That results in 4 with your post #6 data. If you want to count when date planned is the same as date completed then change the < to <=

[josephteh]

Try this:=SUMPRODUCT(((E3:E14="e")*(F3:F14 <=G3:G14))+((E3:E14="e")*(G3:G14="")))

[BB4523]

Thank you for the help!

[BB4523]

Thank you for the help, it worked and I can move on to another part of my worksheet!