Display material with usage

[lintan]

Column A contains material name and Column B contains usage. I would like to
choose the name and the total usage of materials that were used. For
explanation purposes,

Column A Column B
Paint 30
Thinner 15
Paint 10
Thinner 5
Caulk 0

Therefore, the table will contain paint = 40 and thinner = 20. Caulk will
not be displayed since it had zero usage.

 

[Max]

One way ..

In Sheet1
------------
Assume data below is in A2:B6

Paint 30
Thinner 15
Paint 10
Thinner 5
Caulk 0

etc

Put in say, D1:

=IF(OR(COUNTIF($A$2:A2,A2)>1,B2=0),"",ROW())

Copy down by as many rows as data is expected
in cols A and B, say down to D100

In Sheet2
-------------

Select A2:A100
(# of rows similar to col D in Sheet1)

Put in the formula bar:

=IF(ISERROR(MATCH(SMALL(Sheet1!D,ROW(A1:A100)),Sheet1!D,0)),"",INDEX(She
et1!A:A,MATCH(SMALL(Sheet1!D,ROW(A1:A100)),Sheet1!D,0)))

Array-enter the formula with CTRL+SHIFT+ENTER
instead of just pressing ENTER

Put in B2:

=IF(A2="","",SUMIF(Sheet1!A:A,A2,Sheet1!B:B))

Copy down to B100

You'll get the desired results
(for the sample data)

Paint 40
Thinner 20
<Rest are blanks>

Adapt to suit

 

[Biff]

Hi!

Create a new list that contains only the unique items from
the material list. You can do this by using an advanced
filter. Instructions here:

http://contextures.com/xladvfilter01.html#FilterUR

Assume the new list is on Sheet1 starting in cell A1 and
the original data is on Sheet2 in the range A1:B10.

On Sheet1 cell B1 enter this formula and copy down as
needed:

=SUMIF(Sheet2!A$1:A$10,A1,Sheet2!B$1:B$10)

Biff

 

[Van]

If the number of items you are attempting to track is small and based on your
example for the items in Column A and Column B, try the following formulas in
the following cells on your worksheet.

Place this formula in A9
=IF((SUMPRODUCT(($A$2:$A$7="Paint")*$B$2:$B$7)>0),"Paint"," ")
Place this formula in B9
=IF(A9="Paint",SUMPRODUCT(($A$2:$A$7="Paint")*$B$2:$B$7),"")
Place this formula in A10
=IF((SUMPRODUCT(($A$2:$A$7="Thinner")*$B$2:$B$7)>0),"Thinner"," ")
Place this formula in B10
=IF(A10="Thinner",SUMPRODUCT(($A$2:$A$7="Thinner")*$B$2:$B$7),"")

I think you see the pattern now, so you should know how to create the
formula for Caulk.

 

[Max]

Put in say, D1:

=IF(OR(COUNTIF($A$2:A2,A2)>1,B2=0),"",ROW())

Sorry, typo: "Put in D1" above should read as "Put in D2"