0

I'm working since few weeks with google sheets & encountered an issue, which i wasn't able to find a solution for.
I have a small inventory file with tabs for each received qty (received) , outgoing qty (listing), with a check true/false check to if payment is received (listing column S) & a inventory tab with unique items.

Inventory tab formula

=UNIQUE(Received!B6:B)

=SUMIFS(Received!$I$6:$I,Received!$B$6:$B,A19)

=countifs(Listings!$B$6:$B,VLOOKUP(A19,Listings!$B$6:$B,1,0),Listings!$S$6:$S,TRUE)

However I understood that due the change of data in the received tab (placement of items with different items in the same line) cause issues on the inventory, as the UNIQUE function, will not update the replaced Data (item) & therefore incorrectly counts the previous data, which is based on the line (A19) which is not available anymore.

Is there any way to either, be able to use a different function to have the inventory to stay accurate, even if changes in lines are done in received? or to update the countifs formula to find the only the current (updated) line are counted& not the old date.

2
  • 1
    Are you just trying to get an on-hand inventory count? I don't fully understand the concept here, sorry. As for "update the countifs to find current" I might suggest QUERY instead of COUNTIFS if you need more advanced criteria.
    – Grant
    Jan 1 at 20:11
  • Hi and welcome to WebApps. With the greatest respect, by listing existing formulas, you have NOT explained EXACTLY what you are trying to do, what transaction data is on each sheet, and what does a successful outcome look like. For example: COUNTIFS - what is the intended purpose of this?; and due the change of data in the received tab (placement of items with different items in the same line) - I guess that this is a customer shipment of several products, but what is the layout of this sheet? Please edit your question to include sample data and an example of the expected outcome.
    – Tedinoz
    Jan 2 at 0:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.