IF(COUNTIFS......Help Request

Options

Main Sheet:


My Current Data Sheet:


*I already have a Formula on the Data Sheet for "Client" to look at the Main Sheet and if a Client Name is the same, return the value once. And for "Deliverable" formula to return the count value of a deliverable if "Status" not equal to 'Waiting on Client'


Now I want to add an additional Column to my Data Sheet called "Late". I am trying to have the "Late" column to provide me the count of each late deliverable if the "Deliverable" is not blank and Status is not equal to 'Waiting on Client' with the "Due Date" is less than Today.

I tried the Formula below and I know it is missing AND "Status" is not equal to 'Waiting on Client'

=IF(COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>"") = 0, "", COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>""))

The End Result should look like this:

Thank you advance on any suggestion.

Best Answer

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Answer ✓
    Options

    =IF(COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>"",{Status},<>"Waiting on Client") = 0, "", COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>"",{Status},<>"Waiting on Client"))

    This should works. Have you tried it?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!