How to Use Excel to Help Calculate Inventory Classification
Excel is a program that always seems to have one more trick that can make your life easier. One of the more useful ones is the use of conditional array functions, like =COUNTIF() and =SUMIF() to complete complex (and tedious) things very simply, like tracking inventory classifications.
Other People Are Reading
Instructions
-
-
1
Open Excel, and fill in the following information: In cell A1, type "Sales Rep"; in A2 put "Car Type"; and in cell A3, put "Quarterly Sales." These are your headers for the rows to follow.
-
2
In the second row, enter "Xander," "Compact" and "64,000" in the columns. Enter "Willow," "Compact," and "88,000" in row three. Enter "Spike," "Truck," and "120,000" in row four. And enter "Buffy," "Minivans," "24,000" in row five.
-
-
3
Enter "Total Compact Car Sales" in cell D7. In cell E7, enter the following formula: "=SUMIF(B2:B5,"Compact",C2:C5)". This formula will only total the sum of the numbers in column C if the word "Compact" appears in column B.
-
4
Enter "Number of Sales Reps Selling Compacts" in cell D8. In cell E8, enter the following formula: "=COUNTIF(B2:B5,"Compact")". This formula counts the number of times the word "Compact" appears in column B.
-
1
Tips & Warnings
While the example here is very basic, the trick to both of these functions is that you can tell Excel to count instances of a given item (useful for tracking, say, the number of sales orders for a specific part number), or to sum the results pertaining to a particular cue (much like in the example where the number of sales of Compact cars were tallied up).
