Countifs with an IF statement and dates

Hi, I have two dates in my sheet - a final filing date and an extended final filing date - I am trying to identify the number of advertisements that are closed (for clean ads)- I can count with this countifs statement the FFD but don't know how to factor in the extended final filing date when that field contains a date (is., is not blank).

=COUNTIFS([CLEAN POSITION]:[CLEAN POSITION], 1, [FINAL FILING DATE]:[FINAL FILING DATE], <TODAY(-1), [APPLICANT/CANDIDATE NAME]:[APPLICANT/CANDIDATE NAME], "ADVERTISEMENT")

Any help would be greatly appreciated!


Thank you all!

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @DHR Temp Assignment Team

    Hope you are fine, Could you please share a screen shot of your sheet after removing any sensitive data.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • sorry, I am unable to upload a picture for some reason with this tool - here's the columns


    Final Filing Date Extended Final Filing Date Clean (check box)

    2/26/21 3/5/21 Checked


    Looking to count the rows that have Clean checked and a blank EXT FFD would count only the FFD; if there is a EXT FFD it would count the EXT FFD

  • sorry, I am unable to upload a picture for some reason with this tool - here's the columns


    Final Filing Date Extended Final Filing Date Clean (check box)

    2/26/21 3/5/21 Checked


    Looking to count the rows that have Clean checked and a blank EXT FFD would count only the FFD; if there is a EXT FFD it would count the EXT FFD

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @DHR Temp Assignment Team

    Please send to my Email

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!