SOLVED

Help required - multiple formulas needed

Brass Contributor

Good day,

 

I have the folloeing problems with an example workbook attached, that I was wondering whether anyone can help with. It involves formulas which I cannot get right/don't understand. Many htnaks in advance.

 

Problem 1)

 

I would like cell B2 from Sheet1 to return a ‘Yes’ if any of the corresponding answers from Sheet2 with the same account numbers match a ‘banana’ in B:B

 

Problem 2)

 

I would like cells c:c from Sheet 1 to return a result of ‘yes’, if the corresponding account number on Sheet2 has a vegetable with 3 labels matching  (would expect just account number 2 to be a ‘yes’

10 Replies

@Robert1290 

1.

=LET(results,UNIQUE(FILTER(Sheet2!$B$2:$B$31,Sheet2!$A$2:$A$31=A2)),IF(XOR(results="banana"),"Yes",""))

 

2. This formula presumes '3 labels matching' means 3 unique labels matching.

=LET(labels,UNIQUE(FILTER(Sheet2!$D$2:$D$31,Sheet2!$A$2:$A$31=A2)),IF(COUNT(labels)>2,"Yes",""))

@Robert1290 

If with pre-DA Excel

=IF( ISNA( MATCH(A2&"banana", Sheet2!A:A&Sheet2!B:B, 0) ), "no", "yes")

=IF( COUNTIFS(Sheet2!A:A,A2, Sheet2!C:C, "vegetable" ) >= 3, "yes", "no" )
Thanks @Sergei Baklan

The first formula doesn't seem to work all of the time, in a few instances I am getting a 'yes', rather than a 'no'.

For the 2nd formula, I am trying to count the number of instances I get a recurring label number (sheet2 d:d), providing that sheet2 c:c is a vegetable, using the same account number fopr both sheets.

I am on 365
@Patrick2788

Thanks for your help, I prefer Sergei's answer for part 1, but you may be closer on the 2nd problem. I am getting a 'Yes' against account numbers 1,3,4 & 5, however I only expect to get it against account number 2, as this is a vegetable, and it has 3 labels of the same value (50)

@Robert1290 

Try this:

=LET(labels,FILTER(Sheet2!$D$2:$D$31,Sheet2!$A$2:$A$31=A2),IF(COUNT(labels)>2,"Yes",""))
@Patrick2788
This is giving a response of 'yes' for each cell, rather than just a 'yes' against c3

@Robert1290 

Updated formula with the "vegetable" criteria.

=LET(labels,FILTER(Sheet2!$D$2:$D$31,(Sheet2!$A$2:$A$31=A2)*(Sheet2!$C$2:$C$31="Vegetable")),IF(COUNT(labels)>2,"Yes",""))
best response confirmed by Robert1290 (Brass Contributor)
Solution

@Robert1290 

In B2:

=IF(COUNTIFS(Sheet2!$A$2:$A$31,A2,Sheet2!$B$2:$B$31,"banana"),"yes","no")

In C2:

=IF(COUNTIFS(Sheet2!$A$2:$A$31,A2,Sheet2!$C$2:$C$31,"vegetable")>2,"yes","no")

Fill down.

@Patrick2788
That seems to work, thank you
@Hans Vogelaar
Many thanks Hans, this works on the example. I will run this on the real workbook tomorrow and test. Thanks again, I'm a noob to the IFS formula, although much better than last year (I still use that VBA code you wrote).
1 best response

Accepted Solutions
best response confirmed by Robert1290 (Brass Contributor)
Solution

@Robert1290 

In B2:

=IF(COUNTIFS(Sheet2!$A$2:$A$31,A2,Sheet2!$B$2:$B$31,"banana"),"yes","no")

In C2:

=IF(COUNTIFS(Sheet2!$A$2:$A$31,A2,Sheet2!$C$2:$C$31,"vegetable")>2,"yes","no")

Fill down.

View solution in original post