Inventory consolidation template
By the Analistable team · Updated · 3 min read
A free workbook with a Stock counts sheet (one row per location and SKU), a Products sheet holding unit cost and reorder level, and a Summary that uses SUMIFS to show quantity per location, total quantity, stock value and a Reorder flag. Paste each site's count into the same sheet and the summary updates.
Part of our guide: How to consolidate data in Excel
What the template does
When each branch or warehouse sends its own count sheet, the usual result is a summary built by copying totals across by hand. This template takes a different route: every count line goes into one long table with a Location column, and the summary is calculated from it. It is the template version of combining branch inventory counts, and it follows the stacking approach in the consolidating data in Excel guide.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Stock counts | Location, SKU, Qty counted, Count date, Known SKU | Yes — first four columns |
| Products | SKU, Product, Unit cost, Reorder level | Yes — your product list |
| Summary | SKU, Product, one column per location, Total qty, Unit cost, Stock value, Reorder level, Status | SKUs and location names only |
How to fill it in
- Keep the product list on Products: SKU, name, unit cost and the level at which you reorder.
- Paste each location's count under the previous one on Stock counts, writing the location name on every row.
- Look at Known SKU: 'Unknown SKU' means a typo or a product missing from Products.
- On Summary, the location names in row 1 (C1 to E1) must match the Location column exactly. Add a column for each extra site.
How the formulas work
Each location cell reads its location name from the header row, so one formula fills the whole grid:
=SUMIFS('Stock counts'!$C$2:$C$500,'Stock counts'!$A$2:$A$500,C$1,'Stock counts'!$B$2:$B$500,$A2)Unit cost and reorder level come from Products with INDEX/MATCH:
=IFERROR(INDEX(Products!$C$2:$C$200,MATCH($A2,Products!$A$2:$A$200,0)),0)Status compares total quantity with the reorder level:
=IF(F2<I2,"Reorder","OK")Worked example
Three locations count six products. The Summary sheet shows:
| SKU | Product | Leeds | Bristol | Warehouse | Total | Value | Reorder at | Status |
|---|---|---|---|---|---|---|---|---|
| SKU-101 | Desk lamp | 12 | 8 | 25 | 45 | 810 | 30 | OK |
| SKU-102 | Office chair | 4 | 3 | 6 | 13 | 1105 | 10 | OK |
| SKU-103 | Monitor stand | 9 | 0 | 14 | 23 | 552 | 20 | OK |
| SKU-104 | USB-C hub | 15 | 11 | 10 | 36 | 540 | 40 | Reorder |
| SKU-105 | A5 notebook | 60 | 45 | 0 | 105 | 262.5 | 100 | OK |
| SKU-106 | Cable tidy | 0 | 20 | 18 | 38 | 114 | 50 | Reorder |
Total stock value is 3,383.50. A zero means the location had no count line for that SKU — worth checking whether it is really out of stock or just missing from the sheet.
Limits and when to move on
This works well for a handful of sites and a few hundred SKUs. A consolidated count is not a reconciliation: to compare it with what your stock system says, follow reconciling a stock count with inventory records. If you receive dozens of count files each week, stack them with Power Query's From Folder, or load the files into Analistable and ask which SKUs fall below reorder level across all sites without copying anything into this sheet.
Frequently asked questions
- Can I add more locations?
- Yes. Insert a column on Summary before Total qty, type the location name in row 1, copy the SUMIFS formula across, and extend the Total qty SUM to include it.
- What if the same SKU appears twice for one location?
- SUMIFS adds both lines together, which is right for counts split across shelves. If it is a duplicate paste, delete the extra row.
- Does it handle different units of measure?
- No. Convert every count to the same unit as the unit cost before pasting, or the stock value will be wrong.