Formula to check a box based on two conditions

Charles_M

I have a sheet with a client name on the sheet 1. I want to check a box on sheet 1 based on the values on the CRM sheet. If the CRM sheet has the client name on sheet 1 and the offer status column is "Accepted" on the CRM sheet, I want to check a box on sheet 1 next to the clients name. They may be listed in the CRM sheet multiple times but only one row will match the condition, and they will only be on sheet 1 one time. I am trying variations of this this but it's not working:

=IF(AND({Client Name on CRM} = [Client Name]@row, {CRM Range Offer Status} = "Accepted"), 1, 0)

Paul Newcome

Try this instead:

=IF(COUNTIFS({Client Name on CRM}, @cell = [Client Name]@row, {CRM Range Offer Status}, @cell = "Accepted")> 0, 1, 0)

Paul Newcome

Try this instead:

=IF(COUNTIFS({Client Name on CRM}, @cell = [Client Name]@row, {CRM Range Offer Status}, @cell = "Accepted")> 0, 1, 0)

Charles_M

Thanks Paul! Got it working with this!

Paul Newcome

Happy to help. 👍️

popsical547

Paul, how do i see if the reference and value matching in two work sheets?

popsical547

Paul Newcome

@popsical547 I'm not sure I understand your question.

Stacey Carrasco

@Paul Newcome what if I have duplicate values when searching the source sheet. So I don't have 2 conditions to search for instead I notice my issue is the sheet to search has duplicate values, which for me would be an email is being used twice so the box wont check instead it returns #Boolean Expected error in the checkbox field.

If their is no duplicate value the box is checking and the formula is working nicely. I wonder if there is a way for the box to check even if there is a duplicate value or I guess I need to modify this formula to take into account duplicates =COUNTIFS({Conf Emails}, Email@row)

Paul Newcome

@Stacey Carrasco I would use an IF statement to say that IF the COUNTIFS is greater than or equal to 1 then check the box.

=IF(COUNTIFS(……….) >= 1, 1)

Stacey Carrasco

@Paul Newcome That was the trick!!! That works now!
thanks again for always getting me to the finish line 😁

Paul Newcome

Happy to help. 👍️