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

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

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

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

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

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

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