Skip to content

Count Inventory Based on Part of an SKU

Here’s a fun challenge!

Let’s say you have all of your products listed in a spreadsheet. Let’s also assume that you have created a unique and meaningful SKU for each product as well.

Now all that’s left is to get an inventory count of products that have an SKU starting with a specific prefix such as ABC or DEF.

How do you go about getting that inventory count? Keep reading to find out!

The Formula

You can use the following formula to get a sum of the inventory for products with a specific SKU prefix.

=SUMIF(range_of_SKUs, SKU_prefix & "*", qty_range

How the Formula Works

Applied to the actual data, the formula looks like this.

The SUMIF function looks at the range of SKUs via the first argument.

The second argument tells Excel to consider only the cells containing values that start with the value in E2.

The asterisk tells Excel that it doesn’t matter what else comes after that.

In other words, the second argument reads like this. The criteria range must start with whatever is in E2, and anything else is ok to come after that.

The final and third argument represents the sum range.

The formula does its thing and calculates the total inventory for products with an SKU prefix of ABC.

So the next time you are faced with a challenge like this, you will know how to solve it!

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top