 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / December 2005Subtotaling 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...  SignatureJ. Paul Long Training Manager 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$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. > 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 [quoted text clipped - 18 lines] > then have the formula calculate a total for all the like SKUs...? I'm > totally lost on what will work... 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? SignatureJ. Paul Long Training Manager > 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 [quoted text clipped - 46 lines] > > then have the formula calculate a total for all the like SKUs...? I'm > > totally lost on what will work... 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),"") > Thank you for the help. It is greatly appreciated. I have a couple of > questions... pasting the first formula gets me the SKU information; [quoted text clipped - 54 lines] > > > then have the formula calculate a total for all the like SKUs...? I'm > > > totally lost on what will work... 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 > 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, [quoted text clipped - 67 lines] > > > > then have the formula calculate a total for all the like SKUs...? I'm > > > > totally lost on what will work... 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. > 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 [quoted text clipped - 18 lines] > then have the formula calculate a total for all the like SKUs...? I'm > totally lost on what will work... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.