Forum Discussion
Solved
Excel - Obtaining Data From A Table
Good Afternoon
In the table below. I would like to:
- Count the quantity of items of the same size in column A and put into a separate cell.
- Count the quantity of items of the same size in column A and multiply them by the length and put into a separate cell.
Any help is very much appreciated.
Qty | Item | Length |
4 | 100 x 133 x 20 | 5559 |
1 | 100 x 133 x 25 | 5349 |
1 | 100 x 133 x 15 | 2602 |
2 | 100 x 133 x 30 | 2602 |
2 | 100 x 133 x 20 | 5559 |
2 | 100 x 133 x 25 | 5349 |
7 | 100 x 133 x 15 | 2602 |
2 | 100 x 133 x 30 | 2602 |
=LET(qtyrqd,A6:A416, item,B6:B416, length,D6:D416, itemlength,UNIQUE(B6:D416), itemonlyitem,FILTER(UNIQUE(B6:D416),{1,0,0}), itemConly,FILTER(UNIQUE(B6:D416),{0,1,0}), itemonlylength,FILTER(UNIQUE(B6:D416),{0,0,1}), itemlengthqty,FILTER(SUMIFS(qtyrqd,item,itemlength,length,itemonlylength),{1,0,0}), multiply,itemlengthqty*itemonlylength, result,CHOOSE({1,2,3,4,5},itemlengthqty,itemonlyitem,itemConly,itemonlylength,multiply), finalresult,SORT(FILTER(result,itemlengthqty>0),2), finalresult)This formula seems to return the expected result in my spreadsheet.
13 Replies
=LET(qtyrqd,A2:A9, item,UNIQUE(B2:C9), itemdescription,FILTER(item,{1,0}), itemqty,FILTER(SUMIF(B2:B9,item,qtyrqd),{1,0}), itemlength,FILTER(UNIQUE(B2:C9),{0,1}), itemqtylength,itemqty*itemlength, result,CHOOSE({1,2,3},itemqty,itemdescription,itemqtylength), result)Is this what you are looking for? This formula seems to work in my spreadsheet as shown in the attached file.
- Thank you for your reply.
I have inputted your formula into the actual spreadsheet. It works however there are two issues that occur.
1) When the contents of the item column are listed they are duplicated a number of times.
This may be due to there being empty rows in the actual spreadsheet that are not in the above example.
2) Line 2 and line 5 of your formula refers to cells B2:C9. In the actual spreadsheet there is another column in the table. When I input B2:B9,D2:D9 instead of B2 to C9 an error message occurs.
Is there anyway to resolve these issues?Can you share your actual spreadsheet with all columns and rows including the empty rows?
- i am also not clear what he wants, may be i think this is what he wants.
but always learning from you guys. 🙂- Thank you for your replies. I trust the below clarifies what I am looking for.
In the table below is the data that I have.
Qty
Rqd. Item Length
4 100 x 133 x 20 5559
1 100 x 133 x 25 5349
1 100 x 133 x 15 2602
2 100 x 133 x 30 2602
2 100 x 133 x 20 5559
2 100 x 133 x 25 5349
7 100 x 133 x 15 2602
2 100 x 133 x 30 2602
I would like a formula that lists the the data into cells as per below:
Total
Qty
Rqd. Item Total Length
6 100 x 133 x 20 33354
3 100 x 133 x 25 16047
8 100 x 133 x 15 20816
4 100 x 133 x 30 10408
If possible I would like to have the items in smalles size to largest,
