Completely stumped on this formula

Paige_Hamby

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.

Carson Penticuff

@Paige_Hamby

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

You can try it with HAS.

=IF(AND({Customer Name} = Customer@row, {Document Type} = "Upsell", HAS({Item Purchase}, "SummitIT Upsell")), "Upsell", "")

Paige_Hamby

@Carson Penticuff Just tried that and was hopeful it would work, but it gave me #INVALID OPERATION

Carson Penticuff

@Paige_Hamby

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", "")

Paige_Hamby

@Carson Penticuff YAY!!!! THAT WORKED :) thank you so much. Didn't even think about trying to do a countif statement in that.