SUMIFS function returns Zero value

Question

The SUMIFS function return zero value since one of the parameters do not exist for a specific value.

How can I modify the function so when it will not encounter one of the parameters, it will not return zero value but just refer to the parameters that do exist.

=SUMIFS('GL Account Details'!$AB:$AB,'GL Account Details'!$N:$N,Sheet3!A4,'GL Account Details'!$A:$A,Sheet3!$H$1,'GL Account Details'!$A:$A,Sheet3!$H$2)

The parameter "Sheet3!$H$2" do not exist for the specific value "Sheet3!A4". For different values the "Sheet3!$H$2" will be relevant

Answer 1

At first glance, that SUMIFS formula will never produce anything but zero unless Sheet3!H1 and Sheet3!H2 are the same value. If they are different then the value in 'GL Account Details'!A:A cannot be both at the same time; since you can never make a match, you never get anything but zero.

You can introduce an OR statement into the SUMIFS function by wrapping it in a SUMPRODUCT function.

=SUMPRODUCT(SUMIFS('GL Account Details'!$AB:$AB,
                   'GL Account Details'!$A:$A, Sheet3!$H$1:$H$2,
                   'GL Account Details'!$N:$N, Sheet3!A4))

      

Alternately, you can simply add two SUMIFS together.

=SUM(SUMIFS('GL Account Details'!$AB:$AB,
            'GL Account Details'!$A:$A, Sheet3!$H$1,
            'GL Account Details'!$N:$N, Sheet3!A4)),
     SUMIFS('GL Account Details'!$AB:$AB,
            'GL Account Details'!$A:$A, Sheet3!$H$2))