ABC Inventory Analysis

Excel Formulas › Retail & Inventory

2010+SUMIF

ABC analysis ranks items by their share of value so you focus on the vital few. Compute each item’s cumulative percentage of total value and label A (top ~80%), B, or C with nested IFs.

Quick formula: classify by cumulative value share:
=IF(cum% <= 80%, "A", IF(cum% <= 95%, "B", "C"))
Sort by value descending, take the running percent of total, then band into A/B/C.

Functions used (tap for the full reference guide):

SUMIFIF

The example

Cumulative value drives the class.

AB
1Cum %Class
270%A
390%B
499%C

The formula

The formula:

=IF(B2<=0.8, "A", IF(B2<=0.95, "B", "C")) // banding by cumulative %

How it works

How it works:

  1. Compute each item’s annual value (usage × unit cost) and sort descending.
  2. Build a running percentage of the grand total: cumulative_value / SUM(values).
  3. Band with nested IF: A for the top ~80% of value, B next ~15%, C the rest.
  4. A items are few but high-value — tight control; C items are many but low-value — loose control.

The 80/20 in action: ABC is Pareto applied to inventory — typically ~20% of SKUs drive ~80% of value (class A). Counting and tightly managing those few items captures most of the benefit, while class C can run on simple reorder rules. Adjust the 80/95 cutoffs to your catalog.

Try it: interactive demo

Live demo

Cumulative percentage of value.

%
Class:

Variations

Item value

Usage × cost:

=annual_usage * unit_cost

Cumulative percent

Running share:

=SUM($C$2:C2) / SUM($C$2:$C$100)

Count per class

How many As:

=COUNTIF(class_col, "A")

Pitfalls & errors

Sort first. Cumulative percent only makes sense with items ordered by value descending.

Lock the total. Use absolute references for the grand total in the running percent.

Cutoffs are choices. 80/95 is conventional — tune to your catalog.

Practice workbook

📊
Download the free ABC Inventory Analysis practice workbook
An ABC-analysis sheet with the item-value, cumulative-percent, and count-per-class variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I do ABC analysis in Excel?
Compute each item's value, sort descending, build a running percent of total, then band with =IF(cum%<=0.8,"A",IF(cum%<=0.95,"B","C")).
What do A, B, and C mean?
A items are the high-value vital few (top ~80% of value), B the next ~15%, C the low-value many. Control tightness follows the class.
Can I change the cutoffs?
Yes — 80/95 is conventional. Adjust the thresholds in the IF to fit your catalog's value distribution.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Nested IF · Cumulative percent of total · Rank values

Function references: SUMIFIF