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 "checked-in" 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 "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
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 "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
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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!