Excel Count行，其中A列中的值在B列中有重复项，并且该重复项的其他值在A列中等于 [英] Excel Count rows where value in Column A has a duplicate in column B and that duplicate's other value in Column A equals something
问题描述
给出下表:
| A | B | C | D |
|---|---|---|---|
| 1 | 开心 | 1-蔬菜 | GHF |
| 1 | 悲伤 | 1-蔬菜 | HGF |
| 2 | 生气 | 1-蔬菜 | GHG |
| 2 | 悲伤 | 1-蔬菜 | FGH |
| 3 | 悲伤 | 1-蔬菜 | HGF |
| 4 | 喜怒无常 | 2肉 | FFF |
| 4 | 悲伤 | 2肉 | HGF |
| 5 | 激动 | 2肉 | HGF |
我需要计算C ="1-vegies"，B ="sad"，A在A内重复且A在A中的替代行在D ="GHF"中具有值的行数.或"FGH".
I need to count the number of rows where C="1-veggies", B="sad", A has a duplicate within A and whose alternative rows in A have a value in D = "GHF" or "FGH".
结果应基于第二行为1.
The result should be 1 based on the second row.
第二行具有B ＝悲伤"，C ＝"1-蔬菜"，A在A内具有重复项，其值是"1".并且第一行也具有A ＝"1".的值为D ="GHF".
The second row has B= "sad", C = "1-veggies", A has a duplicate within A, whose value is "1" and the 1st row, also with A = "1" has a value in D = "GHF".
我已经有一个公式，如下:
I already have a formula as follows:
=SUMPRODUCT((B1:B8="sad")*(COUNTIF(A1:A8,A1:A8)>1)*(C1:C8="1-veggies")*(D1:D8="GHF"))
但是，它无法检查第一行，也无法检查D列上必要的OR语句.
However, it isn't able to check the 1st row nor does it check the necessary OR statement on column D.
我不知道该如何完成这两项任务.谢谢.
I don't know how to accomplish either task. Thank you.
推荐答案
添加更多COUNTIFS():
Add some more COUNTIFS():
=SUMPRODUCT((B1:B8="sad")*(COUNTIF(A1:A8,A1:A8)>1)*(C1:C8="1-veggies")*(COUNTIFS(A1:A8,A1:A8,B1:B8,"<>sad",D1:D8,"GHF")+COUNTIFS(A1:A8,A1:A8,B1:B8,"<>sad",D1:D8,"FGH")>0))
这篇关于Excel Count行，其中A列中的值在B列中有重复项，并且该重复项的其他值在A列中等于的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
