CODEWITHSUNDEEP
google-sheets
F IT
F IT

Reputation: 21

A google Sheets method to count between different sheets?

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

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 |        |         |
.
.
.

Upvotes: 1

Views: 77

Answers (1)

user3511512
user3511512

Reputation: 115

I think the following formula would work

=countifs(Sheet1!A$2:A,A2,Sheet1!C$2:C,"<>") + COUNTIFS(Sheet1!A$2:A,A2,Sheet1!B$2:B, "", Sheet1!C$2:C,"")

Upvotes: 2

Related Questions