# Help with IF COUNT IFS Cross Reference Formula

Options
edited 02/21/23

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

• ✭✭✭✭✭✭
Options

@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)

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

@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)

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• edited 02/21/23
Options

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

Best,

Jessie

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!