Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function and how?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function and how?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function and how?
you can use Data - Filter - Advanced filter to
create a unique list. If the source data is on a different sheet then start the filter operation from the destination sheet. -- Biff Microsoft Excel MVP "Luke M" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function and how?
Hi,
You could create a pivot table. Drag list to the row area and quantity to the data area -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Stevet" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function and how?
Thanks guys for your prompt replies, Luke, your idea was close but not quite
what I wanted, T. Valko, I tried using the 'Advanced Filter' but didn't quite get it to work how I wanted, Ashish, I don't know how to use pivot tables, so didn't try. Fortunately, All this help that you guys have written has caused me to stumble across 'Consolidate' which is exactly what I needed. Thankyou once again for your help -- Kind regards, Stevet "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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which function and how?
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 |
#7
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 |
Reply |
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 |