Need A Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is my first post so pardon me if I don't do this correctly! I have a spreadsheet with columns of products & columns of quantities. The quantity column has been calculated via a VLOOKUP formula.

I need a formula to then analyze the list of products and make a summary for a pick ticket. So there may be several entries of the same item in the products list but with different quantities. Is there a way to automatically sum the quantites of all the like items? Here's what it looks like:
Product Qty
c-130 2
c-130 1/2
c-110 3

I'd like to end up with c-130 2 1/2 & c-110 3. There's another section where there are 2 separate criteria plus a qty that I need to do the same thing to. Thanks for any help you can offer! - Cindy
 
If you have a list of the unique items, you could use the SumIF formula

=Sumif(A:A,"c-130",B:B)

where "c-130" can be replaced by a cell reference to a cell containing
c-130.

If you need to meet multiple critiera, you can use Sumproduct

=SumProduct((A1:A200=D2)*(B1:B200>3)*(C1:C200))

sums up values in column C where column A equals the value in D2 and column
B is greater than 3.

You also might want to look at using a PivotTable from Data=>Pivot Table
Reports

This will summarize your data in the fashion you have described including
identifying the unique items.

--
Regards,
Tom Ogilvy

Cindy Findley said:
This is my first post so pardon me if I don't do this correctly! I have a
Click to expand...
spreadsheet with columns of products & columns of quantities. The quantity
column has been calculated via a VLOOKUP formula.
I need a formula to then analyze the list of products and make a summary
Click to expand...
for a pick ticket. So there may be several entries of the same item in the
products list but with different quantities. Is there a way to automatically
sum the quantites of all the like items? Here's what it looks like:
Product Qty
c-130 2
c-130 1/2
c-110 3

I'd like to end up with c-130 2 1/2 & c-110 3. There's another section
Click to expand...
where there are 2 separate criteria plus a qty that I need to do the same
thing to. Thanks for any help you can offer! - Cindy
 
I responded once but got an error message, so apologies if this shows up twice.

The problem is that I never know ahead of time what the items will be. It's a tile installation pick ticket pulling products & quantities from a work order template. It's different every time. Without knowing ahead of time what will be on the list, is there a way to automatically summarize the items to pick? That is, I can't have 3 lines of the same grout color & expect the warehouse to add them up on their own. And unfortunately, I know nothing about VBA. Thanks!
 
Yes, as I said, use a pivot Table.

You can get a unique list with an advanced filter under the data menu

You could do it with formulas, but not sure when you want it to happen - do
you want the "form" to automatically calculate.

--
Regards,
Tom Ogilvy

Cindy Findley said:
I responded once but got an error message, so apologies if this shows up twice.

The problem is that I never know ahead of time what the items will be.
Click to expand...
It's a tile installation pick ticket pulling products & quantities from a
work order template. It's different every time. Without knowing ahead of
time what will be on the list, is there a way to automatically summarize the
items to pick? That is, I can't have 3 lines of the same grout color &
expect the warehouse to add them up on their own. And unfortunately, I know
nothing about VBA. Thanks!
 
Back
Top