Which function and how?

  • Thread starter Thread starter Stevet
  • Start date Start date
S

Stevet

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.
 
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".
 
you can use Data - Filter - Advanced filter to
create a unique list.
Click to expand...

If the source data is on a different sheet then start the filter operation
from the destination sheet.
 
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
 
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
 
Back
Top