How to count “No” in column C base on Name & Id?

问题:

I have 3 columns.

-----A-----B-----C-----
---Name----ID---Report

In column A I have Names(Can Repeat)
In column B I have IDs (can repeat with same ID for different names)
In column C I have Yes and No(Can Repeat with NO for same ID)

I need help to count Number of No's in column C for John(Column A) but only count once if column B has duplicate ID.

I can do this via filters and stuff but I need to do it using formula only please.

Here is file I am working on(dropbox link)
Dropbox excel file link

This is what I got so far

=COUNTIF(A1:A1000,"*" & "John" & "*")

回答1:

You can use a single formula like this:

=SUM(IF(FREQUENCY(IF(A2:A200="John",IF(C2:C200="No",B2:B200)),B2:B200),1))

That's an "array formula that needs to be confirmed with CTRL+SHIFT+ENTER

That gives 3 for your example - note that it requires column B data to be numeric which seems to be the case here.....

回答2:

My thought is to add column D with the formula =COUNTIFS(A:A,"="&A2,B:B,"="&B2,C:C,"="&C2). This will give you the number of duplicates for each line. If a line returns a 3, then it means there are two other instances of that exact configuration.

Next, create a new table, for testing I did this in columns G and H. Column G is just numbered 1,2,3 etc. beginning in G2. The Name is a header and reference in cell H1, in this case John. Then put this formula in cell H2: =COUNTIFS(A:A,"="&$H$1,C:C,"=No",D:D,"="&G2)/G2 and drag it down. It is essentially counting the instances of each "1" duplicate, "2" duplicate, and "3" duplicate (which might actually be useful). Then just sum the H column and you should have your answer.

回答3:

This seems to be very close to what you are trying to do

=SUMPRODUCT((A2:A1000="John")*(C2:C1000="No")*(IF(FREQUENCY(B2:B1000,B2:B999)>0,TRUE)))

There is one flaw with the frequency in that it ignores the last row. If I change the frequency formula to FREQUENCY(B2:B1000,B2:B1000) it will error out as it returns 10 results instead of the desired 9.

EDIT

It is probably better to extend the other ranges than shorten the bins array.

=SUMPRODUCT((A2:A1001="John")*(C2:C1001="No")*(IF(FREQUENCY(B2:B1001,B2:B1000)>0,TRUE)))