用多个条件计算不同的值 [英] Counting Distinct Values with multiple criteria

问题描述

我有一个具有以下数据示例的Excel文件：

I have an Excel file with the below data example:

IssueNumber    Client
100            Client 1
100            Client 1
101            Client 1
102            Client 2
102            Client 2

我想计算每个客户端的唯一IssueNumbers数量。所以最终的结果将是：

I want to count the number of unique IssueNumbers for each client. So the end result would be:

Client    Count
Client 1  2
Client 2  1

我在主数据的单独标签中列出了客户端，并尝试使用COUNTIFS查找，并将客户名称作为标准之一，但我正在努力找出如何计算独特的问题。

I have a list of the clients in a separate tab from the main data, and am trying to look up using COUNTIFS, and passing the Client name as one of the criteria, but I am struggling to figure out how to count the unique issues.

这一定很简单，但它是星期五下午，我的大脑已经放弃了！

This must be really simple, but it is Friday afternoon and my brain has given up!

推荐答案

更多选项：

=SUM(IF(((MATCH($A$2:$A$6&$B$2:$B$6,$A$2:$A$6&$B$2:$B$6,0))>=(ROW($A$2:$A$6)-(MIN(ROW($A$2:$A$6))-1)))*($B$2:$B$6=D2)=1,1,0))

&

=SUM(IF(FREQUENCY(IF($B$2:$B$6=D2,MATCH($A$2:$A$6&"_"&$B$2:$B$6,$A$2:$A$6&"_"&$B$2:$B$6,0)),ROW($A$2:$A$6)-ROW($A$2)+1),1))

上述公式都是数组公式，所以应该输入按 Ctrl + Shift + 输入。

Both the above formulas are array formula so should be entered by pressing Ctrl+Shift+Enter.

这篇关于用多个条件计算不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！