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

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

Tags:

Answers

  • Jason Tarpinian
    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)

    Jason Tarpinian - Sevan Technology

    Smartsheet Platinum Partner

  • Paul Newcome
    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)