Excel-计算多个条件下的唯一值 [英] Excel - Count unique values on multiple criteria
问题描述
我对此和解决方案示例有点疑惑.有人可以提供针对此特定问题的解决方案吗?一旦看到我需要的东西,它就会点击如何在其他地方使用具有不同变量的东西.各种学习障碍.谢谢你.
My head is just sort of spinning on this and the solution examples. Can someone please provided a solution for this specific issue? Once I see it for what I need then it clicks on how to use elsewhere with different variables. Learning disorder of sorts. Thank you.
A B C D E
First Last Account Transaction Status
Frank Tomas 101 ACH Fraud
Dillon Krege 105 CC-DC
Joann Krimble 103 ACH
Frank Tomas 101 Wire
Clint Edwards 102 ACH Fraud
Joann Krimble 103 CC-DC Fraud
Joann Krimble 103 ACH
使用"ACH"的交易记录(D列)并被编码为欺诈"的状态(E列)的唯一帐户(C列)?
Unique Account (column C) using Transaction (column D) of "ACH" and coded as Status (column E) of "Fraud"?
此表中有3000多个行,如果有区别的话.数据透视表不是一种选择,我可以很快完成.
There are 3000+ rows in this sheet, if that makes a difference. Pivot table not an option, which I could have done quickly.
推荐答案
这与 Scott的响应基本上是相同的方法Craner ,但具有动态列长度.
This is essentially the same method as the response from Scott Craner but incorporates dynamic column length.
=SUMPRODUCT(((D$2:INDEX(D:D, MATCH(1E+99, C:C))=H2)*(E$2:INDEX(E:E, MATCH(1E+99, C:C))=I2))/
(COUNTIFS(C$2:INDEX(C:C, MATCH(1E+99, C:C)), C$2:INDEX(C:C, MATCH(1E+99, C:C)),
D$2:INDEX(D:D, MATCH(1E+99, C:C)), H2, E$2:INDEX(E:E, MATCH(1E+99, C:C)), I2)+
(D$2:INDEX(D:D, MATCH(1E+99, C:C))<>H2)+(E$2:INDEX(E:E, MATCH(1E+99, C:C))<>I2)))
此公式的动态列长确实取决于帐户是否为真数字.
This formula's dynamic column length does depend on the accounts being true numbers.
这篇关于Excel-计算多个条件下的唯一值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!