Help with IF COUNT IFS Cross Reference Formula

Jessie B.
Jessie B. ✭✭
edited 02/21/23 in Formulas and Functions

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!



Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!