| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function and how?
See this:
http://contextures.com/xladvfilter01.html#FilterUR -- Biff Microsoft Excel MVP "Stevet" wrote in message ... Hi all, It seems Consolidate is not quite the function I'd hoped it would be, I can't use it in a formula. Can anybody help with this problem, I have column A and B in another worksheet and I want to condense the list to only the information in columns D and E. I have the formula for column E (thanks to Luke), I now just need the formula for column D. Incidently, the formula in column E is =IF(D2="","",(SUMIF($A$2:$A$12,D2,$B$2:$B$12))) A B D E Fabric Quantity Fabric Quantity Chenille 10 Chenille 230 Chenille 20 Pampas 200 Chenille 30 Aster 230 Pampas 40 Pampas 50 Aster 60 Aster 70 Chenille 80 Chenille 90 Aster 100 Pampas 110 -- Kind regards, Stevet "Luke M" wrote: You'll want to use the SUMIF function. Works like this: =SUMIF(Range_with_criteria,Criteria,Range_to_sum) So, let's say your data is on Sheet 1, and on Sheet 2, you have your list of fabrics starting in A2. Formula in B2 is: =SUMIF('Sheet 1'!A:A,A2,'Sheet 1'!B:B) You can then copy this formula downward and it will return a total for eahc fabric you have listed. ==== To generate unique list (if needed): If you have a lot of fabric types you need to create a list for, you can use Data - Filter - Advanced filter to create a unique list. Select "Copy to location", input the correct ranges for list range and copy range, leaving criteria range blank. Check the box for "unique records only". -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Stevet" wrote: Hello, I'm using Excel 2003 Can anybody help me please, I have a worksheet that has a list of fabric names (with some names duplicated and sometimes in different areas of the list, ie; chenille, chenille, Pampas, Aster, Pampas, Chenille) and next to it is a list of quantities. What I want to do is on another work sheet work out how much chenille, Pampas and Aster without the need to write everything down more than once. so for instance a column with Chenille, Pampas and Aster, and the next column with the corresponding quantities all added up for me. -- Kind regards, Stevet |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Threaded Mode
