问题描述
我有四个开发人员的列表（A1 =Dev1，A2 =Dev2，A3 =Dev3和A4 =Dev3）。在工作周日历中，我有5列，每周一次（C1 =星期一，D1 =星期二，E1 =星期三，F1 =星期四，G1 =星期五）。
如果开发者Dev3在星期二休息一天，我会去D2并输入：Dev3。
在上面的情况下，我的值为1，如果我将D2单元编辑为Dev3，Dev4，则H2的结果将为 2'。
这个公式对我需要的效果很好，但是我知道有一个更优雅的方式，我可以使用A：A列的开发人员列表，而不是每个开发人员创建一个COUNTIF元素。
任何人都可以帮助我实现g使用列表A：A而不是为每个开发人员创建单个COUNTIF元素？
¹
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:
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?
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 ))
¹
这篇关于COUNTIF公式具有多个条件和表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！