Formula for counting past end/start dates and checkbox left unticked

Abigail Whitfield

Hello,

I'm trying to create a formula to flag if a start/end date is in the past and the started/complete checkboxes haven't been ticked. I have conditional formatting to show this but I want to count how many red flags I have across my sheet and I understand I can't use formulas to count conditional formatting, but I can create a formula taking into account my formula and count that. Please can you help me determine what formula I should be using?

Many thanks in advance,

Abi

Jason Tarpinian

To have the the Symbol type column show a flag if a date is in the past and another column is not checked you can use the formula:

=IF(AND([Start Date]@row < TODAY(), [Started?]@row <> 1), 1, 0)

Then you can use the formula below to count how many flags you've got.

COUNTIF(Flagged:Flagged, 1)

Paul Newcome

You could also include all variables into a single COUNTIFS.

=COUNTIFS([Start Date]:[Start Date], @cell < TODAY(), [End Date]:[End Date], @cell < TODAY(), Started:Started, @cell <> 1, Complete:Complete, @cell <> 1)