0

I am putting together an Inventory Excel Sheet for a Pharmacy. First I have a sheet with the list of Antibiotics:Running Totals Sheet

Then I have a Inventory that contains the Invoice Number and other information based on the antibiotics bought: Inventory Sheet

First, I'd like to add the total amount of the medication bought, so if for instance I bought 150 Amoxicilin that is spread over 2 invoices, I'd like a total (150 pills). Then I have this other sheet which is a log of how many sold: Sold Items Log. I'd like to subtract the amount sold from the total and display the amount currently in stock. Any help appreciated.

Improve this question

1 Answer 1

Reset to default
0

Using SUMPRODUCT solves the issue:

:Caveat:

For clear view and better understanding I've used single screen/window, and few of the necessary data only.

enter image description here

How it works:

  • Formula in Sheet 1 cell B29:

    =SUMPRODUCT((Sheet2!$A$40:$A$43=$A29)*(Sheet2!$C$40:$C$43))

  • Formula in Sheet 1 cell C29:

    =$B29-SUMPRODUCT((Sheet3!$A$47:$A$48=$A29)*(Sheet3!$B$47:$B$48))

Adjust cell references in the formula as needed.

Improve this answer
2
  • THANK YOU, yes this is exactly what I need!
    – Kristin Vernon
    Nov 24, 2020 at 20:08
  • @KristinVernon ,, glad to help you,, keep asking ☺
    – Rajesh Sinha
    Nov 25, 2020 at 6:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .