Subtotaling list of different items

[JP Long]

The data below shows a section of the information we are trying to subtotal.
What I am looking for is a formula (or combination of formulas) that will
provide a subtotal for each specific item listed. An example is illustrated
on the first two SKUs.


30478FJYPK20MIN EA 275
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3 30478FJYPK20MIN = 384
30518FRTRP SET 2
30518FRTRPHC SET 4 30518FRTRP = 6
3068FM100 EA 358
3068FM100 EA 8
3068SPEM4P EA 112
3068SPEM4P20MIN EA 275
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21

I think what I want to do is search the sheet for each specific item and
then have the formula calculate a total for all the like SKUs...? I'm
totally lost on what will work...

 

[Kleev]

I modified something I was using which was similar (except I was just doing
counts.) If you don't mind the information being in 2 columns and the SKU
being listed first instead of last like so:

30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
30478FJYPK20MIN EA 21
30478FJYPK20MIN EA 85
30478FJYPK20MIN EA 3
30518FRTRP SET 2 30518FRTRP 2
30518FRTRPHC SET 4 30518FRTRPHC 4
3068FM100 EA 358 3068FM100 366
3068FM100 EA 8
3068SPEM4P EA 112 3068SPEM4P 112
3068SPEM4P20MIN EA 275 3068SPEM4P20MIN 363
3068SPEM4P20MIN EA 85
3068SPEM4P20MIN EA 3
3068SPEN4P20MIN EA 21 3068SPEN4P20MIN 21

Also notice I did not put in the = sign. I'm sure I could have using the
concatenation symbol (&), but just in case you wanted to use the numbers as
numbers, I figured if you really wanted, you could use a column with a very
small width and put the "=" sign there.
The two formulas are:
=IF(COUNTIF(OFFSET(D$23,-1,0),A3)>0,"",A3)
=IF(COUNTIF(A$3:A$15,D3)>0,SUMIF(A$3:A$15,D3,$C$3:$C$15),"")
Note: It appears that you need to put those formulas in rows 3 or below or
you will get a bunch of #REF errors.

 

[JR]

I assume you want to sum the numbers for a given sku, and not count the
sku's. If so, you will need a list of the individual sku numbers to use as
criteria for a SUMIF formula. =SUMIF($A$1:$A$75,E1,$C$1:$C$75) What this
says is you are looking for the criteria (E1) in the column A range, when you
find it, sum the values in the column C range. The ranges need to be
"locked" with dollars signs so you can copy the formula down to subsequent
criteria without them re-adjusting themselves. Another way to do this would
be to run a Pivot Table on the data.

 

[JP Long]

Thank you for the help. It is greatly appreciated. I have a couple of
questions... pasting the first formula gets me the SKU information;
however,t he second formula does not generate any information. What exactly
is the second formula calculating (which data)?

When pasting this formula, will I need to paste it in a cell for each
different SKU or is there a way to automate this entry?

 

[Kleev]

The second formula goes to the right of the first formula (in the next
column.) It is calculating the total (384 for the first row of data.) Then,
just copy the formulas down. The second formula refers to the cells that the
first formula is in (to see if there is data in the cell next to it) so that
it only shows data when there is something in the previous column.
Let's see. Here is the data with the cells showing the results of the
formulas:
30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
And here is the data with the cells showing the actual formulas (of course,
it is wrapping so take that into account. Hope this helps clear it up.

30478FJYPK20MIN EA 275 =IF(COUNTIF(OFFSET(D$23,-1,0),A3)>0,"",A3) =IF(COUNTIF(A$3:A$15,D3)>0,SUMIF(A$3:A$15,D3,$C$3:$C$15),"")

 

[Kleev]

After looking over your questions again, I guess I should have mentioned that
the formulas do not repeat data so you can copy them to consecutive rows
without worrying about SKUs or totals repeating. Also, just to clarify, the
data and formulas I used started in row 3. I'll paste a couple of rows and
include Column and row labels to see if that helps. Also, I assume you know
that the ranges I used were representative of the test data and would need to
be expanded for your data.

A B C D E
3 30478FJYPK20MIN EA 275 30478FJYPK20MIN 384
4 30478FJYPK20MIN EA 21
5 30478FJYPK20MIN EA 85
6 30478FJYPK20MIN EA 3
7 30518FRTRP SET 2 30518FRTRP 2
8 30518FRTRPHC SET 4 30518FRTRPHC 4
9 3068FM100 EA 358 3068FM100 366
10 3068FM100 EA 8
11 3068SPEM4P EA 112 3068SPEM4P 112
12 3068SPEM4P20MIN EA 275 3068SPEM4P20MIN 363
13 3068SPEM4P20MIN EA 85
14 3068SPEM4P20MIN EA 3
15 3068SPEN4P20MIN EA 21 3068SPEN4P20MIN 21

And now just 2 rows with the formulas showing:
A B C D E
3
30478FJYPK20MIN EA 275 =IF(COUNTIF(OFFSET(D$23,-1,0),A3)>0,"",A3) =IF(COUNTIF(A$3:A$15,D3)>0,SUMIF(A$3:A$15,D3,$C$3:$C$15),"")
4
30478FJYPK20MIN EA 21 =IF(COUNTIF(OFFSET(D$24,-1,0),A4)>0,"",A4) =IF(COUNTIF(A$3:A$15,D4)>0,SUMIF(A$3:A$15,D4,$C$3:$C$15),"")


HTH