Help with IF COUNT IFS Cross Reference Formula

Jessie B.

I have been struggling with an If/COUNT IFS cross reference formula. I am trying to create an item check in and out system where the item and the status are repeating down one sheet and inventory is kept on another sheet (see images).

What I want the sheet to do, is if the criteria in the formula is met, even if multiple times, that it will return a "1". If it's not met, for example, if the "Checked out" column in the second sheet is checked, but not the "checked-in" column, then I want the inventory sheet (the first sheet) to return a zero.

Any help would be much appreciated!

Thanks,

Jessie

Jeff Reisman

@Jessie B. So essentially, if you find at least one unchecked "Checked-In" box for IDAHO Banquet Tablecloth on a row where "Checked-Out" is checked, your value should be 0, right? Then it really doesn't matter how many rows have both checked, or how many have neither checked. So let's just count the ones we care about.

=IF(COUNTIFS({Tradeshow Materials Reference}, "IDAHO Banquet Tablecloth", {Checked Out Reference}, true, {Checked In Reference}, false) > 0, 0, 1)

Jeff Reisman

@Jessie B. So essentially, if you find at least one unchecked "Checked-In" box for IDAHO Banquet Tablecloth on a row where "Checked-Out" is checked, your value should be 0, right? Then it really doesn't matter how many rows have both checked, or how many have neither checked. So let's just count the ones we care about.

=IF(COUNTIFS({Tradeshow Materials Reference}, "IDAHO Banquet Tablecloth", {Checked Out Reference}, true, {Checked In Reference}, false) > 0, 0, 1)

Jessie B.

@Jeff Reisman, you are correct, and this worked! Thank you so much!

Best,

Jessie