CODEWITHSUNDEEP
excel
SammyDow
SammyDow

Reputation: 49

Distinct count with conditions

I am referring to the following post : Excel distinct count with conditions

I would like to do the same thing that has been asked and answered by user Chris Neilsen, however there is a little twist. 

A                 
sp24679999        
sp24661009        
sp24661003        
sp24660009        
sp24660003        
1231244545        
1231244545       

B
YES
YES
NO
YES
NO
NO
NO

I would like to count how many different values there are in column A containing "NO" in column B. The problem here is that there are not only values in column A, but there is also text. Here answer should be 3

Any help would be GREATLY appreciated
Thank you very much

Upvotes: 1

Views: 511

Answers (1)

JMax
JMax

Reputation: 26601

I don't think it will be easy to use the FREQUENCY formula here because you have text.

Here is a try in another way:

{=SUM(IF(B1:B15="NO",IF(COUNTIFS(A1:A15,A1:A15,B1:B15,"NO")>1,1/COUNTIFS(A1:A15,A1:A15,B1:B15,"NO"),1),0))}

[EDIT] French version (as the OP has a french version of Excel)

=SOMME(SI(B1:B15="NO";SI(NB.SI.ENS(A1:A15;A1:A15;B1:B15;"NO")>1;1/NB.SI.ENS(A1:A15;A1:A15;B1:B15;"NO");1);0))

Validate as an array formula with Ctrl + Shift + Enter

The formula will count for a total of 1 each couple of unique in col A / "NO" in col B, and 1 if there is only one couple.

I've tested if on a sample of data.

I hope I didn't mess up my formula translation. Please tell me if anything goes wrong.

Upvotes: 1

Related Questions