Formula If & AND referencing form another sheet
Is there a formula for the following scenario referencing from another sheet?
if "checkbox = ticked under Unique column" AND "Fruit = Apple" Then display [color]@row
Refer to below, result I want to get is "Green"
=IF(AND({SheetA Range 3}=1, {SheetA Range 2}=Apple), {SheetA Range 4})
I tried with above formula, but didnt work. =(
Best Answers
-
Try something along the lines of...
=INDEX(COLLECT({Sheet A Color}, {Sheet A Unique}, 1, {Sheet A Fruit}, "Apple"), 1)
-
I think I found a solution without Unique column.
=COUNT(DISTINCT(COLLECT({Sheet A Fruit}, {Sheet A Color}, ="Yellow")))
-
Yes. The COUNT(DISTINCT(COLLECT(....))) should work. Using the Unique column would look something along the lines of...
=COUNTIFS({Sheet A Unique}, 1, {Sheet A Color}, "Yellow")
Answers
-
Try something along the lines of...
=INDEX(COLLECT({Sheet A Color}, {Sheet A Unique}, 1, {Sheet A Fruit}, "Apple"), 1)
-
Woot! It works! Thanks heaps!
-
Happy to help! 👍️
-
hi Paul
Another weird scenario.
if "checkbox = ticked under Unique column" AND "color = Yellow" Then count the number of yellow.
Refer to below, result I want to get is "3"
=INDEX(COUNTIFS({Sheet A Color}, {Sheet A Unique}, 1, {Sheet A Color}, "Yellow")
I guess adaptation formula above doesnt work. T.T
-
I think I found a solution without Unique column.
=COUNT(DISTINCT(COLLECT({Sheet A Fruit}, {Sheet A Color}, ="Yellow")))
-
Yes. The COUNT(DISTINCT(COLLECT(....))) should work. Using the Unique column would look something along the lines of...
=COUNTIFS({Sheet A Unique}, 1, {Sheet A Color}, "Yellow")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 66 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!