Excel：如果另一列重复则仅计数一次 [英] Excel: Count Only Once If Another Column Has Duplicates

问题描述

我正在尝试创建一个公式，该公式将计算过期人数而不是过期单元格的数量。每个人都有多行，其中有他们的名字。确定是否为过期的标准是：

I am trying to create a formula that will count the number of "Overdue" people instead of "Overdue" cells. Each person has the multiple rows with their name in it. The criteria to determine if it's "overdue" is:

-F列和G列不在3年之内

-Column F and G are not within 3 years

-G列为空白

-F列为F且G列不在3年之内

-Column F = 0 and Column G are not within 3 years

以下代码准确地计算了标准。并且不考虑重复的名称。

The following code counts the criteria accurately. And doesn't account for duplicate names.

= SUM（COUNTIFS（TT [Fiscal Law 301 CBT]，<&今天（）-1065，TT [财政法301 CBT]，>& TODAY（）-1095，TT [财政法驻地]，<& TODAY（）-1065，TT [财政法]驻地]，>& TODAY（）-1095），COUNTIFS（TT [驻省财政法》，，TT [驻省法律301 CBT]，<& TODAY（）- 1065，TT [财政法律301 CBT]，>& TODAY（）-1095））

我想要的公式应该根据上述条件进行计数，并检查 Z列 TT [名称] 中是否有重复项，并且只计数一次。并且还计算一次唯一值。

The formula I want should count based on the criteria above and check column Z TT[Name] for duplicates and count them only once. And also count unique values once.

我只是想到了另一种方法问题。如果我使用必须创建一个数组的公式，又有另一个公式计算该数组的列Z 中重复的数量，然后减去两个数字，该怎么办？

I just thought of another approach to this problem. What if I use the formula I have to create an Array, have another formula count the number of duplicates in Column Z from that Array, and subtract the two numbers.

这种方式应该给我正确的数字，我仍然需要帮助来创建第二个公式。

This way should get me the correct number, i will still need assistance creating the second formula.

推荐答案

您已经很近了，但是还不够。

You are close, but not quite there.

使用上面的countifs公式。然后，使用逻辑if（countcell> 0，1，0）在其旁边创建一列。如果该人至少有1本书逾期，则返回1，否则返回0。然后，取该列的总和。

Use a countifs formula like above. Then, create a column next to it with the logic if(countcell>0, 1, 0). This will return 1 if the person has at least 1 book overdue, and 0 otherwise. Then, take the sum of that column.

我提供了一个我们将使用的代码的屏幕截图（E和F列是日期）。如您所见，G列计算了多少本过期的书。如果每个用户至少有1本书过期，则H列提供1。然后，我们可以简单地对H列求和以找到拥有过期书的用户总数。

I have provided a screenshots of the code that we would use (the E and F columns are dates). As you can see, the G column counts how many books are overdue. The H column provides a 1 if at least 1 book is overdue for each user. Then, we can simply sum the H column to find the total number of users with overdue books.

如果这对您有帮助，请考虑选择此作为答案。

If this helps you, please consider choosing this as the answer.

这篇关于Excel：如果另一列重复则仅计数一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！