CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
roger
roger

Reputation: 79

Single occurrence of a value in a column

In a column I have some values, some of which only occur once, others occur multiple times. I want to identify the values that only occur once, and then count the number of those values depending on them having a specific value in an adjacent column i.e. 

Col A............Col B   
John.............8              
John.............2   
Phil.............1   
Bill.............4   
Dick.............1    
Dick.............2

For example, I want to find the values in column A that appear only once (i.e. Phil and Bill), and for those values, I want to identify the ones that have a value of 4 in column B

In this case, the answer to my question is 1 there is only one instance of a value in Col A that is a single occurrence, and which has a value of 4 in Col B

I want to be able to do this in Google Sheets!

Upvotes: 1

Views: 775

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(QUERY(VLOOKUP(QUERY(QUERY({A2:B},
 "select Col1,count(Col1) 
  group by Col1"),
 "select Col1 
  where Col2=1"), A2:B, {1,2}, 0),
 "where Col2=4"))

0

Upvotes: 1

Related Questions