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

问题描述

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

我如何修改函数，以便当它不会遇到参数之一时，它不会返回零值，而只是引用确实存在的参数.

=SUMIFS('GL 帐户详细信息'！$AB:$AB,'GL 帐户详细信息'！$N:$N,Sheet3!A4,'GL 帐户详细信息'！$A:$A,Sheet3!$H$1,'GL 帐户详细信息'！$A:$A,Sheet3!$H$2)

对于特定值Sheet3!A4"，不存在参数Sheet3!$H$2".对于不同的值，Sheet3!$H$2"将是相关的

解决方案

乍一看，

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

=SUM(SUMIFS('GL 账户详细信息'！$AB:$AB,'GL 帐户详细信息'！$A:$A，Sheet3！$H$1，'GL 帐户详细信息'！$N:$N, Sheet3!A4)),SUMIFS('GL 账户详细信息'！$AB:$AB,'GL 帐户详细信息'！$A:$A，Sheet3！$H$2))

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

解决方案

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

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