Completely stumped on this formula
Hello,
I'm trying to be able to identify when an upsell comes through for a specific function. But so far everything I've tried hasn't worked. The sheet I want the information to show up on isn't the sheet that has the data so I have to reference a separate sheet. But basically what I'm trying to do is: =IF(AND({Customer Name} = Customer@row, {Document Type} = "Upsell", {Item Purchase} = "SummitIT Upsell"), "Upsell", ""). It keeps telling me invalid operation. {Customer Name} is a text field and matches the name on the source sheet, {Document Type} is a single select dropdown, and {Item Purchase} is a multiselect dropdown. I do think I need to be able to add a Contains statement for item purchase incase they select SummitIT Upsell with something else.
Best Answer
-
I will admit, I was "Completely Stumped" for a moment as well... but I believe this should work.
=IF(COUNTIFS({Customer Name}, Customer@row, {Document Type}, "Upsell", {Item Purchase}, HAS(@cell, "SummitIT Upsell")) > 0, "Upsell", "")
Answers
-
You can try it with HAS.
=IF(AND({Customer Name} = Customer@row, {Document Type} = "Upsell", HAS({Item Purchase}, "SummitIT Upsell")), "Upsell", "")
-
@Carson Penticuff Just tried that and was hopeful it would work, but it gave me #INVALID OPERATION
-
I will admit, I was "Completely Stumped" for a moment as well... but I believe this should work.
=IF(COUNTIFS({Customer Name}, Customer@row, {Document Type}, "Upsell", {Item Purchase}, HAS(@cell, "SummitIT Upsell")) > 0, "Upsell", "")
-
@Carson Penticuff YAY!!!! THAT WORKED :) thank you so much. Didn't even think about trying to do a countif statement in that.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!