Excel-计算符合多个条件的唯一值 [英] Excel - Count unique values that meets multiple criteria

问题描述

我有5列：

季度，项目，类型，计数，日期

Quarter, Item, Type, Count, Date

我正在尝试为每个季度& amp;拉一个唯一的日期计数项目组合
即。 FY20Q3-AU有2个不同的日期，FW20Q3-GLW有1个唯一的日期，FY20Q3-GLE也只有1个唯一的日期

I am trying to pull a UNIQUE count of date for each Quarter & Item combination ie. FY20Q3-AU has 2 different dates, FW20Q3-GLW has 1 unique date, FY20Q3-GLE also has only 1 unique date

有人知道我如何做到这一点吗？ ？我已经尝试了几个小时，修改了在网上找到的没有成功的公式。

Does anyone have any idea how I can accomplish this? I have been trying for a few hours modifying formulas that I found online without success.

如果可能的话，我正在尝试不使用数组公式的情况（不确定是否甚至有可能）

If possible, I am trying to do this without an array formula (not sure if that is even possible)

我已经尝试过：

{=SUM(--(FREQUENCY(IF(A:A=A2,MATCH(B:B,B:B,0)),ROW(B:B)-ROW(B2)+1)>0))}

---

---

| Quarter | Pricelist | Change Type | Item Count | Date    |
|---------|-----------|-------------|------------|---------|
| FY20Q3  | AU        | Type 1      | 1          | 4/18/20 |
| FY20Q3  | AU        | Type 1      | 1220       | 4/4/20  |
| FY20Q3  | CH        | Type 2      | 2          | 4/11/20 |
| FY20Q3  | CH        | Type 1      | 1378       | 4/4/20  |
| FY20Q3  | GLA       | Type 1      | 1          | 4/18/20 |
| FY20Q3  | GLA       | Type 1      | 1421       | 4/4/20  |
| FY20Q3  | GLB       | Type 1      | 1250       | 4/4/20  |
| FY20Q3  | GLB       | Type 2      | 1          | 4/4/20  |
| FY20Q3  | GLC       | Type 1      | 1          | 4/18/20 |
| FY20Q3  | GLC       | Type 1      | 1404       | 4/4/20  |
| FY20Q3  | GLH       | Type 2      | 2          | 4/11/20 |
| FY20Q3  | GLH       | Type 1      | 1387       | 4/4/20  |
| FY20Q3  | GLME      | Type 1      | 1          | 4/18/20 |
| FY20Q3  | GLME      | Type 1      | 1421       | 4/4/20  |
| FY20Q3  | GLEE      | Type 1      | 1          | 4/18/20 |
| FY20Q3  | GLEE      | Type 1      | 1227       | 4/4/20  |
| FY20Q3  | GLEU      | Type 1      | 1          | 4/18/20 |
| FY20Q3  | GLO       | Type 1      | 211        | 4/4/20  |
| FY20Q3  | GLK       | Type 1      | 1          | 4/18/20 |
| FY20Q3  | GLC       | Type 1      | 1          | 4/18/20 |
| FY20Q3  | GLW       | Type 1      | 1          | 4/18/20 |
| FY20Q3  | GLU       | Type 1      | 1          | 4/18/20 |
| FY20Q3  | GLZ       | Type 1      | 1          | 4/18/20 |
| FY20Q3  | JP        | Type 1      | 1          | 4/18/20 |
| FY20Q3  | NHI       | Type 1      | 1          | 4/18/20 |
| FY20Q3  | NHI       | Type 1      | 1          | 4/18/20 |
| FY20Q3  | WCH       | Type 2      | 2          | 4/11/20 |
| FY20Q3  | WCH       | Type 2      | 2          | 4/11/20 |

推荐答案

因此，由于您可以访问O365，所以它是DA-不再需要CSE输入公式的函数。您可以使用 UNIQUE 和 FILTER 。例如：

So since you got access to O365 and it's DA-functions you would not need a CSE entered formula nomore. You can utilize UNIQUE and FILTER. For example:

=COUNTA(UNIQUE(FILTER(E:E,(A:A=A2)*(B:B=B2))))

在这里，我建议您不要使用整列引用以免中断性能下降太多。

Where I would suggest you won't use whole column references to not break down performance too much.

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