COUNTIFS Using Multiple Criteria

Hi,

Is it possible to use COUNTIFS with multiple criteria.

I would like to count the number of dates in one column that are past the dates in another column, however if a date cell in either column is blank I don't want it to be included in the count.

Thanks

Richard

Answers

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    You would include a not(isblank() in your formula. https://help.smartsheet.com/function/isblank

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • Richard King
    Richard King ✭✭✭✭

    Hi Holly,

    Thanks for the quick response.

    I have tried NOT(ISBLANK(@cell) as in the formula below, but the return count is Zero and should be around 500.

    =COUNTIFS({Helpdesk Orders Report Range 1}, >{Helpdesk Orders Report Range 2}, {Helpdesk Orders Report Range 2}, NOT(ISBLANK(@cell), {Helpdesk Orders Report Range 1}, NOT(ISBLANK(@cell))))

    Thanks

    Richard

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Hi @Richard King,

    It might be easier, since you're referencing another sheet, to add a helper column on the referenced sheet. The column could be yes/no, or a checkbox - whatever your preference. You would make the helper formula if(isblank(date@row)), "yes", "no").

    Then you could reference in the countifs on your secondary sheet that range being = "yes"

    Also, best practice is to change the name of external links so it's easier for other users (and yourself) to tell what data is being pulled in by the formula. It might help you down the road!

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • Richard King
    Richard King ✭✭✭✭
    edited 09/08/21

    Hi Holly,

    I did try a helper column in the source sheet using the formula below. This worked and returned the relevant "comment".

    =IF([Technically compl. date]@row > [Target Completion]@row, "Completed Out of SLA", "Completed in SLA")

    The only issue with the above is when a date is missing from a cell the the formula returns a "comment" that might not be the correct one.

    I then tried the formula below but this returns blank.

    =IF([Technically compl. date]@row > [Target Completion]@row, AND([Technically compl. date]@row, NOT(ISBLANK(@cell), AND([Target Completion]@row, NOT(ISBLANK(@cell), "Completed Out of SLA", "Completed in SLA")))))

    Is it not possible to use multiple IF statements to return one of two "comments"?.

    Thanks

    Richard

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!