Formula for CountIf One column has a value and another column is not blank
![Lisa Matthews](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hello,
I'm reaching out to the community for help with creating two separate sheet summary formulas (see example in image below), to:
- Count the "red" values in the "ryg" column ONLY IF the "New Target Finish" column value is NOT blank
- Count the number of blank cells in the "New Target Finish" column
Thank you, in advance, for helping me with these formulas. The "blank" values + multiple COUNTIF conditions are stumping me and I keep getting #unparseable errors so far.
--Lisa M
Best Answer
-
Hey @Lisa Matthews
Try this
=COUNTIFS(ryg:ryg, "Red", [New Target Finish]:[New Target Finish], @cell<>"")
=COUNTIFS([New Target Finish]:[New Target Finish], @cell = "", [Task Name]:[Task Name], @cell <> "")
For your empty Target Finish count I added your primary column to the formula as a filter - otherwise the formula would find the empty cells all the way down the sheet regardless if you were using the row or not.
Do these work for you?
Kelly
Answers
-
Hey @Lisa Matthews
Try this
=COUNTIFS(ryg:ryg, "Red", [New Target Finish]:[New Target Finish], @cell<>"")
=COUNTIFS([New Target Finish]:[New Target Finish], @cell = "", [Task Name]:[Task Name], @cell <> "")
For your empty Target Finish count I added your primary column to the formula as a filter - otherwise the formula would find the empty cells all the way down the sheet regardless if you were using the row or not.
Do these work for you?
Kelly
-
Hello @Kelly Moore
These formulas are exactly what I need! I just copied and pasted them directly into the sheet summary field and they work perfectly! Thank you for adding in the condition for the Task Name, it is the right solution. I appreciate your help so much!
--Lisa
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!