Excel - Obtaining Data From A Table

[Zephyr360]

Good Afternoon

In the table below. I would like to:

1. Count the quantity of items of the same size in column A and put into a separate cell.
2. 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 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

[#2]

@Zephyr360 
hi zephyr360,
check this out, you want this same result?

[#3]

Thank you for your reply. Your solution requires additional cells on each row.

What I require are single cells at the bottom of the table, each with a single answer.

[#4]

Could you show a little sample, so that i can accomplish what u want

[#5]

@Zephyr360 

=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.

[#6]

i am also not clear what he wants, may be i think this is what he wants.
but always learning from you guys. :)

[#7]

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?

[#8]

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,

[#9]

@Zephyr360 

Can you share your actual spreadsheet with all columns and rows including the empty rows?

[#10]

@OliverScheurich

The spreadheet is attached as requested.

The issues are detailed in a previous post and it would assist if the items were tabulated if possible from smallest size to largest size.

Many thanks for your assistance with this request.

[#11]

@Zephyr360 

2	100 x 10	FB	550
2	100 x 10	FB	550
1	100 x 10	FB	560
1	100 x 10	FB	560

In your actual spreadsheet there are same items with different length, for example 100 x 10   FB with 550 and 560.

Which is your expected result? Do you want to return:

4     100 x 10  FB    2200

2     100 x 10  FB    1120 

or

6     100 x 10  FB    3320

[#12]

@OliverScheurich

The result required is to provide the total number of each size required.
For example there are
100 x 10 - 550mm Long,
100 x 10 - 250mm Long
100 x 10 - 560mm Long Items.

I would like to have a total number required for each size, and a total length of material for each size.

So the output would show for example:

48 100 x 10 FB 250 12000
20 100 x 10 FB 480 9600

48 and 20 are the totals for each item size and the 12000 and 9600 are the length required.
The output would list the smalles items forst to the largest items.

Many Thanks

[#13 · Accepted solution]

@Zephyr360 

=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.

[#14]

@OliverScheurich

The formula is just what I was looking for. Many thanks for you assistance and the time you spent answering my queries is very much appreciated. It will save hours of time going forward.