Formula Counting blank values W/ =COUNT(DISTINCT(COLLECT
I'm trying to collect data from other sheet and COUNT with DISTINCT.
EXAMPLE 1: =COUNT(DISTINCT(COLLECT({NAM-SERIAL}, {NAM-RTS-FW}, [FW11 2024]76, {NAM-FW-OFF}, <>"")))
EXAMPLE 2: =COUNT(DISTINCT(COLLECT({NAM-SERIAL}, {NAM-RTS-FW}, "11", {NAM-FW-OFF}, <>"")))
EXAMPLE 3: =COUNT(DISTINCT(COLLECT({NAM-SERIAL}, {NAM-RTS-FW}, <>"", {NAM-FW-OFF}, <>"")))
EXAMPLE 4: =COUNT(DISTINCT(COLLECT({NAM-SERIAL}, {NAM-RTS-FW}, "", {NAM-FW-OFF}, <>"")))
Only example 4 does not work. WHY? :(
In example 4, I want the formula to only consider what is BLANK in the {NAM-RTS-FW} reference. But it always gives an error and returns the value "1", which is incorrect (when I go to the sheet and do the filter, more than 100 different lines appear).
I've already tried all these options in the formula, and it only returns "1" or some error:
{NAM-RTS-FW}, "",
{NAM-RTS-FW}, ="",
{NAM-RTS-FW}, ISBLANK(@cell)
ISBLANK({NAM-RTS-FW})
{NAM-RTS-FW}, CONTAINS("", @cell)
COUNTIF(ISBLANK({NAM-RTS-FW}))
COUNTIFS({NAM-RTS-FW}, "")
COUNTIFS({NAM-RTS-FW}, "", {NAM-FW-OFF}, <>"")
Answers
-
Your formula should be working. I did a quick test on a sample sheet and was able to return "3" with this formula:
Can you by chance share a screenshot of what your reference sheet looks like? And is the formula you build showing a 1, or a #ERROR of sorts?
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
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!