IF function - is there a way to avoid repeating formula

Question

Can't believe I don't know this, but is there a way to avoid repeating a formula in an if statement if the logical test is dependent on it?

i.e.

=IF((SUMIFS formula)=0,"",SUMIFs formula)

I want to replace that SUMIFS function in the false scenario with something short that will tell it to just programmatically repeat the formula it originally tested for. Repeating the formula twice has to have detrimental effects on processing speed. Negligible, maybe, but want to go for best-practices here. Thanks.

Answer 1

You can force an error like #DIV/0! and then use IFERROR, e.g.

=IFERROR(1/(1/SUMIFS_formula),"")

Answer 2

If all you need to do is hide zeroes, there is an easy way:

• Select all cells where you wish to hide zeroes
• Go into Custom Number Formatting
• Set format to "General;General;"

The custom formatting has a structure of [positive numbers];[negative numbers];[zeroes] By making the last part blank you are effectively hiding zeroes, but showing everything else.

The advantage over conditional formatting is that you can use this on any background.

A neat trick which I sometimes use is to hide the cell value completely by using a custom format of ";;;". This way you can put images inside the cells, like the conditional formatting ones, and not see the value at all.

Answer 3

Try using the SUBSTITUTE function like this :

=SUBSTITUTE( VLOOKUP( H4; $D$5:$E$8; 2; 0 ); $H$1; $I$1 )

Here is an example:

Here the formula I don't want to repeat twice is the VLOOKUP function. The result of VLOOKUP is a string found in another table (ex : "Green").

I want to check if that string matches a specific string value in $H$1 (here, "Yellow").

• If it does, SUBSTITUTE replaces it with$I$1 (the error string you want. Here, "FORBIDDEN").
• If it doesn't, it displays the VLOOKUP result string (the normal authorized output, like "Green").

This is useful for me because my actual formula is quite long, so I don't want to write it twice. I also dont want to use two different cells, because I'm already applying this formula on 10 columns, meaning I should add an extra 10 columns to make it work.

Answer 4

In some scenarios, MAX() or MIN() can do a wonderful job.

E.g., something like this:

=IF(SUMIFSformula>0,SUMIFSformula, 0)

Can be shortened to this:

=MAX(0,SUMIFSformula)

Answer 5

Since Excel 2007, the IFERROR statement does what the OP asked. From the help file:

Description: Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. [italics mine]

Syntax: IFERROR(value, value_if_error)

I've since realised that this was already answered by @barry houdini above.

Answer 6

Here is a hack - depending on whether you are just interested in the displayed value, or whether you need to use the value in another formula:

Put your SUMIF formula in the cell (without the IF part)

Create a conditional formatting rule which sets the font color to the background color when the cell value is 0

And hey presto, you get the desired result.

As I said - it's a hack, but it does prevent the double evaluation.

There is no "clean" solution that I am aware of.