Help with IF COUNT IFS Cross Reference Formula
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 "checkedin" column, then I want the inventory sheet (the first sheet) to return a zero.
Any help would be much appreciated!
Thanks,
Jessie
Best Answer

@Jessie B. So essentially, if you find at least one unchecked "CheckedIn" box for IDAHO Banquet Tablecloth on a row where "CheckedOut" 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
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!
Answers

@Jessie B. So essentially, if you find at least one unchecked "CheckedIn" box for IDAHO Banquet Tablecloth on a row where "CheckedOut" 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
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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 218 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!