Check box if multiple conditions are met across 2 sheets

Mark.Hendley

Good morning,

I have 2 sheets

Sheet A: Job #, complete(Checkbox)

Sheet B: Job #, Reason, complete(Checkbox)

The goal is to have a formula that will check the box in the complete column on Sheet A if the Job # matches between the 2 sheets, the Reason column on Sheet B is "Screens", and the complete column on Sheet B is checked.

This is my current formula

=IF(AND(MATCH([Job #]@row, {Sheet A Job #}), {Sheet B Reason} = "Screens", {Sheet B Complete} = 1), 1)

This is the result I'm getting

#INVALID DATA TYPE

I feel like I'm missing something obvious, or maybe there is a completely different way to do this. Any help is appreciated, Thanks!

Adam Murphy

Try =IF(COUNTIFS({Sheet A Job #}, [Job #]@row, {Sheet A Job #}, @cell = "Screeens", {Sheet B Reason}, @cell = "1", {Sheet B Complete}) > 0, 1, 0)

Adam Murphy

Try =IF(COUNTIFS({Sheet A Job #}, [Job #]@row, {Sheet A Job #}, @cell = "Screeens", {Sheet B Reason}, @cell = "1", {Sheet B Complete}) > 0, 1, 0)

Mark.Hendley

@Adam Murphy Of course as soon as I post this I found the answer from another discussion despite spending all morning looking at discussions, your answer is the same answer I got from another post so thanks for confirming it!