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
-
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
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!