Sumproduct of unique values in multiple ranges

[Dan5977]

Hi guys,

Hope you can help. I have a rather complicated (by my standards) spreadsheet and am stuck at this point. Example of type of problem shown below.

So I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.

I'm using Excel 2010.

As always, any assistance would be appreciated.

Thanks,

Dan.

 

[Special-K99]

Best to post a (dummy) spreadsheet not a picture, saves us having to reenter the data.
I'm at work and I cant see that picture. A spreadsheet however. . .

 

[Dan5977]

Understandably it won't let me add attachments. So try this: dummy sheet.xlsx

 

[Dan5977]

Can anybody help please?

 

[XOR LX]

Hi,

That link appears to be to a folder: can you clarify which file within that folder is the pertinent one?

Regards

 

[Dan5977]

Hi,

That link appears to be to a folder: can you clarify which file within that folder is the pertinent one?

Regards

It's not a folder

 

[Dan5977]

Still stuck on this one

 

[Matty]

Not at all pretty, but seems to work.

B11:

=IFERROR(IFERROR(IFERROR(INDEX(B$2:B$7,MATCH(0,IF(B$2:B$7<>"",COUNTIF(B$10:B10,B$2:B$7)),0)),INDEX(D$2:D$7,MATCH(0,IF(D$2:D$7<>"",COUNTIF(B$10:B10,D$2:D$7)),0))),INDEX(F$2:F$7,MATCH(0,IF(F$2:F$7<>"",COUNTIF(B$10:B10,F$2:F$7)),0))),"")

A11:

=IF(B11="","",SUM(IF(B$2:F$7=B11,A$2:E$7)))

Both formulas need committing with CTRL+SHIFT+ENTER before copying down.

Matty

 

[Aladin Akyurek]

[TABLE="width: 294"]
<TBODY>[TR]
[TD="class: xl64, width: 66, bgcolor: transparent"]Qty
[/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"]Length
[/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"]Qty
[/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"]Length
[/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"]Qty
[/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"]Length
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]3500
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]3000
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]980
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]3020
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]1000
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]3020
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]1000
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]3500
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]1243
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]2500
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]3500
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]7
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Desired Result
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]3500
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]3000
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]3020
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]980
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]1000
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]1243
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]2500
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

Add the following code for ARRAYUNION function to your workbook, using Alt+F11.

Now define LenghtData by means of Formulas | Name Manager as referring to:

=arrayunion(Sheet1!$B$2:$B$7,Sheet1!$D$2:$D$4,Sheet1!$F$2:$F$6)

And define Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(LengthData)))

A11, just enter and copy down:

=IF($B11="","",SUMIF($B$2:$F$7,$B11,$A$2:$E$6))

B11, control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$11:B11)<=$A$9, INDEX(LengthData,
  SMALL(IF(FREQUENCY(IF(LengthData<>"", 
  MATCH("~"&LengthData,LengthData&"",0)),Ivec),Ivec),
  ROWS($B$11:B11))),"")

Addendum. See the workbook that implements the set up of above:
https://dl.dropboxusercontent.com/u/65698317/Dan5977 unque items of a multicolumn range.xlsm

 

[Dan5977]

Thanks guys.

That last one in particular looks pretty impressive stuff and appears to solve my problem. I'll try to work it into my ever developing spreadsheet over the next few days.

Thanks so much!