Formula for sheet summary field to count dates in column

Hello,

Can someone help direct me to the right formula for counting the number of dates entered in one column where selected criteria in a second column applies?

I tried this but can't get it to work when I add the AND CONTAINS to the function.

=COUNTIFS([Date MP Reviewed (Kite CoA Quality Appr) / to QA:]:[Date MP Reviewed (Kite CoA Quality Appr) / to QA:], <>"", [Site Rec'd:]:[Site Rec'd:], "F52", AND(CONTAINS([Site Rec'd:]:[Site Rec'd:], "F03")))

I want to get the count on the "[Date MP Reviewed (Kite CoA Quality Appr) / to QA:]" when "[Site Rec'd:]" is F03, F06, F08,and F52.


Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @jeanniesry_4,

    please try the following formula :

    =COUNTIFS([Date MP Reviewed (Kite CoA Quality Appr) / to QA:]:[Date MP Reviewed (Kite CoA Quality Appr) / to QA:], <>"", [Site Rec'd:]:[Site Rec'd:], "F52") 
    + COUNTIFS([Date MP Reviewed (Kite CoA Quality Appr) / to QA:]:[Date MP Reviewed (Kite CoA Quality Appr) / to QA:], <>"", [Site Rec'd:]:[Site Rec'd:], "F03") 
    + COUNTIFS([Date MP Reviewed (Kite CoA Quality Appr) / to QA:]:[Date MP Reviewed (Kite CoA Quality Appr) / to QA:], <>"", [Site Rec'd:]:[Site Rec'd:], "F06") 
    + COUNTIFS([Date MP Reviewed (Kite CoA Quality Appr) / to QA:]:[Date MP Reviewed (Kite CoA Quality Appr) / to QA:], <>"", [Site Rec'd:]:[Site Rec'd:], "F08")
    


    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"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @jeanniesry_4,

    please try the following formula :

    =COUNTIFS([Date MP Reviewed (Kite CoA Quality Appr) / to QA:]:[Date MP Reviewed (Kite CoA Quality Appr) / to QA:], <>"", [Site Rec'd:]:[Site Rec'd:], "F52") 
    + COUNTIFS([Date MP Reviewed (Kite CoA Quality Appr) / to QA:]:[Date MP Reviewed (Kite CoA Quality Appr) / to QA:], <>"", [Site Rec'd:]:[Site Rec'd:], "F03") 
    + COUNTIFS([Date MP Reviewed (Kite CoA Quality Appr) / to QA:]:[Date MP Reviewed (Kite CoA Quality Appr) / to QA:], <>"", [Site Rec'd:]:[Site Rec'd:], "F06") 
    + COUNTIFS([Date MP Reviewed (Kite CoA Quality Appr) / to QA:]:[Date MP Reviewed (Kite CoA Quality Appr) / to QA:], <>"", [Site Rec'd:]:[Site Rec'd:], "F08")
    


    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"

  • jeanniesry_4
    jeanniesry_4 ✭✭✭✭

    @Bassam Khalil - it works!! thank you so much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!