当有两种类型的空值时，Excel逻辑公式为Total [英] Excel Logical Formula for Total when there are two types of null values

问题描述

我需要在Excel中创建一个公式，它将前6个单元格的值相加。复杂的部分是如果只有空值，则总共应该是连字符： - 。我得到这个工作，但后来意识到有两个可能的空值：连字符和空白。这是我为连字符创建的公式作为空值。它的工作方式是我想要的。但是，由于它们是两种类型的空值，所以几个单元格出现了0。

I need to make a formula in Excel which sums the values of the previous 6 cells. The complicated part is that if there are only null values, the total should be a hyphen: "-". I got this to work, but then realized there are two possible null values: hyphen and blank. Here is the formula I created for the hyphen as the null value. It worked how I wanted. But then several cells came out with a "0" since they were summing both types of null values.

=IF(OR(K3 ="-",L3 ="-", M3 ="-", N3 ="-",O3 ="-", P3 ="-"), "-", SUM(K3:P3))

当我意识到有两个空值时，我尝试添加一个AND和另一个OR，但没有得到预期的结果。

When I realized there were two null values, I tried adding an AND and another OR, but neither got the intended result.

=IF(AND(OR(K3 ="-",L3 ="-", M3 ="-", N3 ="-",O3 ="-", P3 ="-"), K3 ="",L3 ="", M3 ="0", N3 ="",O3 ="", P3 =""), "-", SUM(K3:P3))

任何想法如何获得空白价值在哪里？谢谢！

Any ideas on how to get that "blank" value in there? Thanks!

推荐答案

尝试这样：

=IF(SUMPRODUCT((K3:P3="-")+(K3:P3=""))=COLUMNS(K3:P3)*ROWS(K3:P3),"-",SUM(K3:P3))

这是有效计数任何单元格都是数字。如果它是0，或者换句话说，如果没有数字，那么它返回 - 否则返回这些值的总和。

This is effectively counting any cells are numbers. If it is 0, or in other words, if no numbers exist then it returns - else it returns the sum of those values.

或者这个更简洁的公式：

Or this more concise formula:

=IF(SUMPRODUCT(1*(ISNUMBER(K3:P3)))>0,SUM(K3:P3),"-")

这篇关于当有两种类型的空值时，Excel逻辑公式为Total的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！