IF with COUNTIFS

David Sanchez Capdevila

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.

David Sanchez Capdevila

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.

Paul Newcome

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"))

David Sanchez Capdevila

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!!

Paul Newcome

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.