COUNTIFS，ISNUMBER和多个条件 [英] COUNTIFS with ISNUMBER and multiple criteria

问题描述

我试图计算以下范围是"Y" 还是"S" 并包含数字.我正在尝试排除以"_ PVxxxxx _" 开头的单元格.

我不能使用

  COUNTIFS($ A:$ A，> 0"，$ B:$ B，"Y")+ COUNTIFS($ A:$ A，> 0"，$ B:$ B，"S") 

因为该公式认为"_ PVxxxxx _" 大于 0 ，并将它们包括在计算中.

有人可以帮忙吗?非常感谢！

解决方案

函数 SUMPRODUCT 相当通用，通常比 COUNTIFS 和 SUMIFS更灵活地进行测试.

应该可以解决问题(请参见例如 https://stackoverflow.com/a/27935006/2707864 或https://stackoverflow.com/a/30854706/2707864 )与

  = SUMPRODUCT(($ A:$ A> 0)*($ B:$ B ="Y")*(ISNUMBER($ A:$ A))+ ... 

这可行，但是我不确定根据您发布的示例，您是否需要零件($ A:$ A> 0)* (无论如何也不会造成伤害).

PS:如果您坚持使用 COUNTIFS ，则可以使用一个使用 ISNUMBER 的帮助程序列，并给出例如适当的数字结果(对于数字数据，> 0，< 0否则).然后，您将在 COUNTIFS 中引用该列.

I'm trying to count if the following range is "Y" or "S" and contains numbers. I'm trying to exclude cells beginning with "_PVxxxxx_".

I cant use

COUNTIFS($A:$A,">0",$B:$B,"Y") + COUNTIFS($A:$A,">0",$B:$B,"S")

because the formula considers "_PVxxxxx_" to be more than 0 and includes them in the calculation.

Can anybody help? Thanks alot!

解决方案

The function SUMPRODUCT is quite versatile, typically more flexible for testing than COUNTIFS and SUMIFS.

It should do the trick (see e.g. https://stackoverflow.com/a/27935006/2707864 or https://stackoverflow.com/a/30854706/2707864) with

=SUMPRODUCT(($A:$A>0)*($B:$B="Y")*(ISNUMBER($A:$A))+...

This works, but I am not sure that you need the part ($A:$A>0)* according to the sample you posted (it doesn't hurt anyway).

PS: If you insist on using COUNTIFS you could use a helper column that uses ISNUMBER and gives, e.g., a suitable numeric result (>0 for numeric data, <0 otherwise). Then you would refer to that column within COUNTIFS.

这篇关于COUNTIFS，ISNUMBER和多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！