Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotaling list of different items
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... -- J. Paul Long Training Manager |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotaling list of different items
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 a =IF(COUNTIF(OFFSET(D$2:D3,-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. "JP Long" wrote: 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... -- J. Paul Long Training Manager |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotaling list of different items
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" wrote: 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... -- J. Paul Long Training Manager |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotaling list of different items
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? -- J. Paul Long Training Manager "Kleev" wrote: 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 a =IF(COUNTIF(OFFSET(D$2:D3,-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. "JP Long" wrote: 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... -- J. Paul Long Training Manager |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotaling list of different items
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$2:D3,-1,0),A3)0,"",A3) =IF(COUNTIF(A$3:A$15,D3)0,SUMIF(A$3:A$15,D3,$C$3: $C$15),"") "JP Long" wrote: 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? -- J. Paul Long Training Manager "Kleev" wrote: 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 a =IF(COUNTIF(OFFSET(D$2:D3,-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. "JP Long" wrote: 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... -- J. Paul Long Training Manager |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotaling list of different items
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$2:D3,-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$2:D4,-1,0),A4)0,"",A4) =IF(COUNTIF(A$3:A$15,D4)0,SUMIF(A$3:A$15,D4,$C$3: $C$15),"") HTH "Kleev" wrote: 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$2:D3,-1,0),A3)0,"",A3) =IF(COUNTIF(A$3:A$15,D3)0,SUMIF(A$3:A$15,D3,$C$3: $C$15),"") "JP Long" wrote: 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? -- J. Paul Long Training Manager "Kleev" wrote: 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 a =IF(COUNTIF(OFFSET(D$2:D3,-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. "JP Long" wrote: 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... -- J. Paul Long Training Manager |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbering items from a list in order | Excel Discussion (Misc queries) | |||
Compare 2 columns, and create a list of items that are in both lists | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Making list of items to truck monthly usage | Excel Discussion (Misc queries) | |||
Indexing items from a pick list | Excel Discussion (Misc queries) |