COUNTIF公式具有多个条件和表 [英] COUNTIF formula with multiple criteria and table

问题描述

我有四个开发人员的列表（A1 =Dev1，A2 =Dev2，A3 =Dev3和A4 =Dev3）。

在工作周日历中，我有5列，每周一次（C1 =星期一，D1 =星期二，E1 =星期三，F1 =星期四，G1 =星期五）。

如果开发者Dev3在星期二休息一天，我会去D2并输入：Dev3。

= COUNTIF（C2：G2，* Dev1 *）+ COUNTIF（ C2：G2，* Dev2 *）+ COUNTIF（C2：G2，* Dev3 *）+ COUNTIF（C2：G2，* Dev4 *）

在上面的情况下，我的值​​为1，如果我将D2单元编辑为Dev3，Dev4，则H2的结果将为 2'。

这个公式对我需要的效果很好，但是我知道有一个更优雅的方式，我可以使用A：A列的开发人员列表，而不是每个开发人员创建一个COUNTIF元素。

任何人都可以帮助我实现g使用列表A：A而不是为每个开发人员创建单个COUNTIF元素？

解决方案

---

¹_{数组公式需要用 Ctrl + Shift + Enter↵来定稿。一旦正确输入第一个单元格，就可以像其他任何公式一样填充或复制。尝试并减少您的全列引用范围更加密切地表示实际数据的范围。阵列公式对数计算循环，所以将参考范围缩小到最小值是一个很好的做法。请参阅有关更多信息的数组公式的指南和示例。}

I have a list of four developers (A1="Dev1", A2="Dev2", A3="Dev3" and A4="Dev3").

Within a working week calendar, I have 5 columns, one for each day of the week (C1="Monday", D1="Tuesday", E1="Wednesday", F1="Thursday, G1="Friday").

If developer Dev3 has a day off on Tuesday, I'd go to D2 and input: "Dev3".

On H2, I have the following formula:

=COUNTIF(C2:G2,"*Dev1*")+COUNTIF(C2:G2,"*Dev2*")+COUNTIF(C2:G2,"*Dev3*")+COUNTIF(C2:G2,"*Dev4*")

In the scenario above, I'd have the value of H2 being '1'. If I edit D2 cell to something like this: "Dev3,Dev4", the result of H2 would be '2'.

This formula works well for what I need but I know that there is a more elegant way that I could use the list of the developers on A:A column, instead of creating a single COUNTIF element per developer.

Could anyone help me achieving the usage of the list A:A instead of creating a single COUNTIF element for every single developer, instead?

sheet's screenshot:

   

解决方案

Either a SUM/COUNTIF function array¹ formula or a SUMPRODUCT function should be able to count correctly providing there are no 'false positives' like Dev1 found in Dev12.

'array formula
=SUM(COUNTIF(C2:G2, "*"&A$1:INDEX(A:A, MATCH("zzz",A:A ))&"*"))
'SUMPRODUCT
=SUMPRODUCT(--ISNUMBER(SEARCH(A$1:INDEX(A:A, MATCH("zzz",A:A )), C2:G2)))

Note that in both cases, the list of developers from column A has been cut down to the minimum number of cells with,

A$1:INDEX(A:A, MATCH("zzz",A:A ))

    

---

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

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