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屋!