Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. . |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to display gridlines at irregular intervals? | Charts and Charting in Excel | |||
Conditional display of a .jpeg file? | Excel Discussion (Misc queries) | |||
Data Filter - Not all rows in spreadsheet will display in Autofilt | Excel Worksheet Functions | |||
how to display heading of column corresponding to the cell | Excel Worksheet Functions | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |