Trouble with Formula

[HuckFinn]

Ok, I am stuck. My current formula: =IFERROR(IF(COUNTIFS(INDIRECT($D$3),A8,INDIRECT($D$3),">0"),COUNT(IF(INDIRECT($D$3)=$A8,INDIRECT($D$3)))),"--") returns a FALSE, when I assumed it would return a " -- " due to meeting the condition outlined in the formula (i.e., there were no instances that matched A8.

Any thoughts on where I am going wrong?

 

[Joe4]

It is important to understand the differences between IF and IFERROR. They are NOT similar.

The IF function has three arguments:
=IF(condition, what to do if condition is TRUE, what to do if condition is FALSE)

The last argument is optional. If you leave it off, and the condition is not met, the formula will return FALSE, by default.

The IFERROR function has two arguments:
=IFERROR(what to return, what to return if previous argument results in an error)
what to return may be a calculation or a cell reference

So, the second argument is what to return if the first argument results in some sort of error (like #N/A or #DIV/0).

Note that FALSE is NOT an error. If the first argument evaluates to FALSE, that that is what the IFERROR calculation will return.


When trying to debug formulas that aren't working correctly, I usually find the best thing to do is start with the inner-most calculation.
Do that and see what that returns. If that works properly, then keep building out, and check the next calculation.
Continue on until you find the error or get to your complete formula.

 

[HuckFinn]

Ahh. Thank you!

This formula worked:

IF(COUNTIFS(INDIRECT($D$3),A6,INDIRECT($D$3),">0"),COUNT(IF(INDIRECT($D$3)=$A6,INDIRECT($D$3))),"--")

 

[Joe4]

You are welcome.