Display material with usage
 

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.

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:D,ROW(A1:A100)),S heet1!D:D,0)),"",INDEX(She
et1!A:A,MATCH(SMALL(Sheet1!D:D,ROW(A1:A100)),Sheet 1!D: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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"lintan" wrote in message
...
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.

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 he

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

-----Original Message-----
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.


.

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.


"lintan" wrote:

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.

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"

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----