IF with COUNTIFS
Im spending a lot of time trying to find a workaround for this type of formula below:
=IF(COUNTIFS({UNIQUE_NFP_Dashboards data source Range 3}, <>"Complete", {UNIQUE_NFP_Dashboards data source Range 3}, <>"Did not Renew SFC", {UNIQUE_NFP_Dashboards data source Range 1}, "EMEAR-MEA", {UNIQUE_NFP_Dashboards data source Range 2}, "FY22Q3") = 0, "-")
I have used this formula to hide the zeros and to replace them for "-" for design reasons as data is populated in a table in a Dashboard.
Problem is formula is feeding from a data source which I update every week and eventually the formula could be bringing up a value <> than zero. This is the reason why I want to add further statement to make sure that when this happens, the formula will pick up the actual value <> than zero, but so far no luck. I have tried by simply adding [FY22Q3]4, like this below and it brings back error: #CIRCULAR REFERENCE
=IF(COUNTIFS({UNIQUE_NFP_Dashboards data source Range 3}, <>"Complete", {UNIQUE_NFP_Dashboards data source Range 3}, <>"Did not Renew SFC", {UNIQUE_NFP_Dashboards data source Range 1}, "EMEAR-MEA", {UNIQUE_NFP_Dashboards data source Range 2}, "FY22Q3") = 0, "-",[FY22Q3]4)
Any help will be appreciated.
Thanks.
Answers
-
By the way the [FY22Q3]4 is the reference i used to try and tell the fomula ==>go back to the same cell now that its not zero and show it.
-
The basic ides is...
=IF(number producing formula = 0, "-", number producing formula)
This says IF the number producing formula equals zero, output the hyphen, otherwise run the number producing formula.
Number Producing Formula:
COUNTIFS({UNIQUE_NFP_Dashboards data source Range 3}, <>"Complete", {UNIQUE_NFP_Dashboards data source Range 3}, <>"Did not Renew SFC", {UNIQUE_NFP_Dashboards data source Range 1}, "EMEAR-MEA", {UNIQUE_NFP_Dashboards data source Range 2}, "FY22Q3")
Dropped into the above logic:
=IF(COUNTIFS({UNIQUE_NFP_Dashboards data source Range 3}, <>"Complete", {UNIQUE_NFP_Dashboards data source Range 3}, <>"Did not Renew SFC", {UNIQUE_NFP_Dashboards data source Range 1}, "EMEAR-MEA", {UNIQUE_NFP_Dashboards data source Range 2}, "FY22Q3") = 0, "-", COUNTIFS({UNIQUE_NFP_Dashboards data source Range 3}, <>"Complete", {UNIQUE_NFP_Dashboards data source Range 3}, <>"Did not Renew SFC", {UNIQUE_NFP_Dashboards data source Range 1}, "EMEAR-MEA", {UNIQUE_NFP_Dashboards data source Range 2}, "FY22Q3"))
-
Yes thanks i did worked it out over night and indeed that's the correct answer. For some reason it did not take it very well when the =0 is going in first, I had to invert it and do the opposite argument something like <> 0, COUNTIFS({UNIQUE_N....),"-")
So..thanks for the answer!!
-
Happy to help. 👍️
I personally use
=IF(number formula > 0, number formula)
Basically I say that if it is greater than zero then run it. Leaving the "else" portion of the IF statement out means it will default to blank if it is equal to zero. If I wanted it to output a symbol such as a hyphen, I just need to put it at the end. It does the same thing and only saves a few keystrokes. It's just how I have gotten used to doing it because of how I personally build and test formulas.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!