COUNTIFS Using Multiple Criteria

Richard King

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

hollyconradsmith

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

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

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!

Richard King

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