Counting Items On A Spreadsheet

Factual Questions
1

This is not a homework assignment. I’m a small business owner with less than expert skills on MS Excel.

Okay.
I have an Excel spreadsheet from a warehouse I do business with.
I send product into the warehouse for fulfillment.
On a monthly basis, I get a spreadsheet with items received per day, broken down by item SKU.
So a line might read:

DATE | SKU | QTY

09/20/2012 WidgetTypeA 3

So, that’s fine. I know I got 3 of the Type A widget yesterday.
Unfortunately, that widget could have been received on 10 different days.
I’d like to have a formula that would tell me how many of the various types of widgets were received in the month.
I have hundreds of SKUs going in per month, so I’d like to avoid having to manually sort by SKU and then do “SUM” calculations for each SKU by hand.

Clues? Is there a function I should be doing?

2

You can sort by SKU and use the Subtotal function.

Sort the data by SKU
Select the data with headers
from the top bar, select Data then Subtotal

It should give you a total at each change in SKU, and a grand total, which you probably won’t need

3

I’d probably use a pivot table, but that might be more advanced than you need.

4

There is the SUMIF function, but that would require you to know all the SKUs and create a seperate formula for each of them in it’s own cell.

=SUMIF(range,criteria,sum range)

5

Yeah.
I certainly DO know all of the SKUs before I ever send product in., but I’d really like to avoid making a sheet like you describe, unless it would be far easier than I imagine.

6

hogarth is this thread’s winner.
Just spent 10 minutes playing with pivot tables, now this whole project will be easy as cake.

7

The formula for something like SUMIF does not need to be hand-edited in order to get what the OP needs. The criteria can be a cell reference. Here’s what I mean:

Create a column of all your product SKUs in column A. Create a formula in column B that says SUMIF($range,A1,sum range). Paste the formula down the column in B, and Excel will adjust the cell references so that B2 references A2, B3 references A3, and so on. (Using in the range and sum range tells Excel to NOT adjust those cell references as you paste). The result will be that B1 contains the quantity sum for all SKUs matching A1, B2 the sum of SKUs matching A2, etc.

A pivot table may still be the more elegant solution, but I’m not that familiar with their use.

8

Yeah, that is kinda where I was headed, and you completed it. I think the pivot table is the better way to go as you would not need to “program” the first column as a reference.

9

One important thing to remember is that pivot tables do NOT automatically update when the information changes. You have to right-click and select “Refresh Data” if you want it updated.

10

No sweat; I’ll be doing this report once a month, and I’ll be composing the pivot table on the fly.
Only needs two columns, so it isn’t excessive work.