 |
 | 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 / June 2007sum column a if column b, c, d, and e are equal
a b c d e quantity | size | use |part num |frame 9 65.375 Hd 450-026 A 18 67.3437 Hd 451-CG-004 A 1 67.375 Sill 451T-CG-001 A < 2 67.375 Sill 451T-CG-001 A < 3 67.375 Sill 451T-CG-001 A < 18 70.875 WJambl 451T-CG-001 A 9 67.3437 Horz 451T-CG-002 A 18 67.375 Hd 451T-CG-003 A 9 71.625 SillFp 451T-HP-037 A 18 70.875 WJambl 452-145 A 8 30.6875 Hd 450-026 B 8 32.6562 Hd 451-CG-004 B 4 67.3437 Horz 451-CG-004 B 4 67.375 Sill 451T-CG-001 B 4 21.75 Vert 451T-CG-001 B 8 70.875 WJambl 451T-CG-001 B 4 67.3437 Horz 451T-CG-002 B I want to eliminate redundancies while summing column "A" and keeping same comma separated values... I'm using Excel 2003 and have got real close but I just don't know enuf to write SUMIF & filter B, C, D, E ARE EQUAL TIL UNIQUE VALUE AND THEN KEEP GOING? quantity |size |use |part num |frame 9 65.375 Hd 450-026 A 18 67.3437 Hd 451-CG-004 A 6 67.375 Sill 451T-CG-001 A <-------- 18 70.875 WJambl 451T-CG-001 A 9 67.3437 Horz 451T-CG-002 A 18 67.375 Hd 451T-CG-003 A 9 71.625 SillFp 451T-HP-037 A 18 70.875 WJambl 452-145 A 8 30.6875 Hd 450-026 B 8 32.6562 Hd 451-CG-004 B It is data the CAD program puts out but doesn't do this necessary step. Thanks in advance, mike Hi, You can use the Data, Filter, Advanced Filter, Unique records only option. First put one row of titles at the top of each column. Only highlight columns B:E for this source range and use the Filter in place option. You can copy the results with column A to a new location. Cheers, Shane Devenshire > a b c d e > quantity | size | use |part num |frame [quoted text clipped - 37 lines] > Thanks in advance, > mike I'm missing something here, did what you said ( filter in place and unique) but when it comes to column A filter I select only column A and unique only? then copy to another column it just repeats. not summing quantity deleting redundacies one method use two helper columns (E,F?) in E1 =a1 in e2 =if(and(B1=B2,C1=C2,D1=D2),E1+A1,A1) in F1 =if(and(B1=B2,C1=C2,D1=D2),"",1) copy E2 and paste to the end of your data copy f1 and paste to the end of your data select columns E and F copy and paste special values select column F and filter-autofilter select blanks select the visable cells and edit-delete rows remove autofilter select column E copy ans select Cell a1 paste spectial values (It sounds more complicated than it is, but make sure you first try it on a copy of your data) > a b c d e > quantity | size | use |part num |frame [quoted text clipped - 37 lines] > Thanks in advance, > mike one method use two helper columns (E,F?) in E1 =a1 in e2 =if(and(B1=B2,C1=C2,D1=D2),E1+A1,A1)<----- in F1 =if(and(B1=B2,C1=C2,D1=D2),"",1) copy E2 and paste to the end of your data copy f1 and paste to the end of your data select columns E and F copy and paste special values select column F and filter-autofilter select blanks select the visable cells and edit-delete rows remove autofilter select column E copy ans select Cell a1 paste spectial values (It sounds more complicated than it is, but make sure you first try it on a copy of your data) (Me) YOU'VE DONE IT!!! AWESOME!!! Kewl I can't Thank You enuf... A few little tweaks and it worked! this is how it worked for me..... one method use two helper columns (E,F?).......(changed because of there was a row "e") in F1 =A1 in F2 =if(and(B1=B2,C1=C2,D1=D2,E1=E2),F1+A1,A2)<----- in G1 =if(and(B1=B2,C1=C2,D1=D2,E1=E2),"",1) copy F2 and paste to the end of your data copy G1 and paste to the end of your data select columns F and G copy and paste special values select column G and filter-autofilter select blanks select the visable cells and edit-delete rows (execpt the first row) remove autofilter select column F copy ans select Cell a1 paste spectial values Wow, this crash course in EXCEL is still making my head spin. many thanx, mike |
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
|
©2008 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.