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...
=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.
=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.
i am also not clear what he wants, may be i think this is what he wants.
but always learning from you guys. 🙂
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,
