Can someone help me out on this one, I've tried everything I can think of and can't make it work, reference sheet 1 and 2 below.
Desired Behavior for Sheet 2
- Column Issued should count +1 IF Sheet1!Item matches Sheet2!Item AND ( Sheet1!Start is not empty AND Sheet1!End is empty )
- Column On Hand should count +1 IF Sheet1!Item matches Sheet2!Item AND ( (Sheet1!End is not empty) OR (Sheet1!End is empty AND Sheet1!Start is empty) )
Backstory: It's for a basic inventory system; to get a count of what is issued, match the "Item" cell and then count if the return date is blank and the issue date is not. To get a count of what's on hand, match the Item cell and then count if EITHER the return date is not blank OR both the issue date and the return date are blank
I got as far as Sheet2(A2) =countifs('Sheet1'!A4:A,A2) -- so that makes sure the items match and gives me a total count. But then adding the conditional logic to include whether this or that cel is empty and this or that cel is not etc... I just can't figure it out. Would appreciate some tips on solving this one!
Thank you
**Sheet 1**
Item | Start | End
----------------------------
Blah | 2020-1-1 | 2020-1-2 |
Yada | 2020-3-4 | <BLANK> |
Nada | <BLANK> | <BLANK> |
.
.
.
**Sheet 2**
Item | Issued | On Hand
------------------------
Blah | | |
Yada | | |
Nada | | |
.
.
.
