SUMIFS函数返回零值 [英] SUMIFS function returns Zero value

问题描述

SUMIFS函数返回零值，因为其中一个参数对于特定值不存在.

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)

对于特定值"Sheet3！A4"，参数"Sheet3！$ H $ 2"不存在.对于不同的值，"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

推荐答案

乍一看，

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.

您可以将 OR 语句引入

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

或者，您可以简单地将两个SUMIFS一起添加.

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

这篇关于SUMIFS函数返回零值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！