IFNIL formula in Excel

Often you don't want to wrap an IF statement when you just want to turn a 0 result into another value such as blank

me@jaykilleen.com wrote this almost 5 years ago and it was last updated almost 5 years ago.


← Back to the Posts

I'll often use this to replace

=IF(SUMIFS(table1[Revenue],Table1[Country],"Australia")<>0,"No Sales",SUMIFS(table1[Revenue],Table1[Country],"Australia")

with

=IFNIL(SUMIFS(table1[Revenue],Table1[Country],"Australia"),"No Sales")

To implement this create a public function method in VBA using the code below.

Option Explicit


Public Function IFNIL(Value As Variant, Result As Variant) As Variant
  If Value = 0 Then
    IFNIL = Result
  Else
    IFNIL = Value
  End If
End Function