Forum Discussion
Solved
Help required - multiple formulas needed
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 ...
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
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)
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)
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 c3In 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.
- HansVogelaar
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).
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",""))- Patrick2788
That seems to work, thank you