Excel - 根据多个条件计算唯一值 [英] Excel - Count unique values on multiple criteria

问题描述

我的头脑只是在思考这个问题和解决方案示例.有人可以为此特定问题提供解决方案吗?一旦我看到我需要的东西，它就会点击如何在其他地方使用不同的变量.各种学习障碍.谢谢你.

A B C D E首尾账户交易状态弗兰克托马斯 101 ACH 欺诈狄龙克雷格 105 CC-DC乔安·克林布尔 103 ACH弗兰克托马斯 101 电线克林特爱德华兹 102 ACH 欺诈Joann Krimble 103 CC-DC 欺诈乔安·克林布尔 103 ACH

唯一账户(C 栏)使用ACH"的交易(D 栏)并编码为欺诈"的状态(E 栏)?

此工作表中有 3000 多行(如果有区别的话).数据透视表不是一个选项，我可以很快完成.

解决方案

这与来自

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 

Unique Account (column C) using Transaction (column D) of "ACH" and coded as Status (column E) of "Fraud"?

There are 3000+ rows in this sheet, if that makes a difference. Pivot table not an option, which I could have done quickly.

解决方案

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