Share via

How to Calculate FIFO-Based Cost of Goods Sold in Excel Using Formulas Only (No VBA or Power Query)

Mansoor Ali 0 Reputation points
2025-08-02T08:06:40.8366667+00:00

Screenshot_2025-08-02-10-46-01-994-edit_com.microsoft.office.officehubrow

Screenshot_2025-08-02-10-45-25-173-edit_com.microsoft.office.officehubrow

Hello experts,

I am building an inventory costing system in Excel using FIFO (First-In, First-Out) logic, and I want to calculate the total purchase cost of items sold — based strictly on the order of purchase batches.

🔹 My Setup:

I have a Purchase Table like this:

Product Units Price Batch

Mango 10 10 1

Mango 20 12 2

And a Sales Table like this:

Product QTY Sold

Mango 12

🎯 What I Want:

Using formulas only (no VBA, no Power Query), I want to return the total cost of the sold quantity using FIFO. So, in this example:

10 Mangoes are taken from batch 1 @ Rs.10

2 Mangoes from batch 2 @ Rs.12

Total Cost = (10×10) + (2×12) = Rs.124

⚙️ Key Requirements:

The formula must:

Automatically pull quantities from the earliest batches first (FIFO logic).

Handle cases where one sale consumes multiple purchase batches.

Be dynamic for multiple products.

The output should be a single cell that returns the total purchase cost of the quantity sold.

❌ Not Allowed:

VBA

Power Query

Manual batch splitting

Microsoft 365 and Office | Excel | For home | Android

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Thomas4-N 1,095 Reputation points Microsoft External Staff Moderator
    2025-08-02T14:43:15.9333333+00:00

    Hello Mansoor Ali,

    Welcome to Microsoft Q&A.

    I understand what you are trying to do, and I'm happy to help you calculate FIFO-based cost of goods sold in Excel using formulas only.

    Let's say this is your Purchase Table:

    User's image

    For easier understanding, I will add a helper column for cumulative number of units till that batch (CumUnits). Put this in cell E2 and copy down:

    =SUMIF($A$2:A2,A2,$B$2:B2)

    This gives you a running total for each product. First you have 10 units, then every batch will get added:User's image Your sales table is put in column F and G. Now, in cell H2, enter this to get your desired cost:

    =SUMPRODUCT(--($A$2:$A$100 = F2),$B$2:$B$100-IF($E$2:$E$100>G2,$E$2:$E$100-G2,0),$C$2:$C$100)

    Explanation:

    • --($A$2:$A$100 = F2) checks which purchase rows match the product in F2 and converts TRUE/FALSE to 1/0.
    • $B$2:$B$100 - IF($E$2:$E$100 > G2, $E$2:$E$100 - G2, 0) calculates how many units from each batch are actually consumed, based on FIFO.
      • If CumUnits is less, no subtract - count all units till that point.
        • When the cumulative total exceeds the sold quantity, subtract out the extra units.
    • $C$2:$C$100 is the price per unit.
    • SUMPRODUCT multiplies consumed units by price and sums the result.

    User's image I hope this is what you are looking for. If you need any further explanation, feel free to ask.

    Best regards,

    Thomas

    ------------------------------------------------------------------------------------------------------------ 

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

  2. Thomas4-N 1,095 Reputation points Microsoft External Staff Moderator
    2025-08-07T09:26:44.5766667+00:00

    Hi Mansoor Ali,

    Thanks again for sharing your setup. I’d like to follow up with a structured method that uses formulas only, without VBA or Power Query. This approach is based on internal guidance and should help you implement FIFO logic dynamically across multiple products.

    Assumptions:

    Your Purchase sheet contains the following columns:

    • Column A: Product
    • Column B: Units
    • Column C: Price
    • Column D: Batch

    Data starts from row 2, with headers in row 1.

    Steps:

    Sort the Purchase table by Product and Batch in ascending order. This ensures that FIFO logic is applied correctly.

    Add helper columns to the Purchase sheet:

    Column Purpose Formula (Row 2)
    E Cumulative Stock =SUMIFS($B$2:$B2, $A$2:$A2, A2)
    E Cumulative Stock =SUMIFS($B$2:$B2, $A$2:$A2, A2)
    E Cumulative Stock =SUMIFS($B$2:$B2, $A$2:$A2, A2)
    F Previous Cumulative F2: 0 (entered manually), F3:=IF(A3=A2, E2, 0)
    G Sales Quantity =IFERROR(VLOOKUP(A2, Sales!$A$2:$B$100, 2, FALSE), 0)(can also be entered manually)
    H Allocated Sales =MIN(B2, MAX(0, G2 - F2))
    I Allocated Cost =H2 * C2

    Fill these formulas down to row 100 or as needed.

    3.In Sales Sheet, add a Total Cost column (e.g., Column C). For C2 (fill down to C100): 

    =SUMPRODUCT((Sheet1!$A$2:$A$100=A2) * (Sheet1!$H$2:$H$100) * (Sheet1!$C$2:$C$100))

    I think this will help with your excel sheet.

    Best regards,

    Thomas

    ------------------------------------------------------------------------------------------------------------ 

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    0 comments
  3. Ashish Mathur 99,595 Reputation points Volunteer Moderator
    2025-08-09T23:40:07.48+00:00

    Hi,

    Difficult to make sense from small images and a textual description. I solved a similar problem years ago. You may access that solution from here - https://www.ashishmathur.com/valuing-closing-stock-using-fifo-method-of-accounting/

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.