This is not a homework assignment. I’m a small business owner with less than expert skills on MS Excel.
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.
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.