• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT W/ LARGE

N

NRUBEMIT

New Member
Hi and thank you in advance for the help. I have attached a small mockup of the dataset I am working with. I have been trying to use a combination of SUMPRODUCT & LARGE TO find in the dataset the 2nd largest value, summing on value 2, where value 1 = A.


For example, the 2nd largest value that meets the criteria I laid out above is 11,500,000 (or 'Value 1' = A, 'Value 2' = ABC, 'Value 3' = 10,500,000 & 1,000,000).


I've been able to figure our how to combine SUMPRODUCT and LARGE to just get me the 2nd largest value in a dataset, but once I throw in combining items based on 'Value 2', I get thrown for a loop.


VALUE1 VALUE2 VALUE3

A ABC 10,500,000

A ABC 1,000,000

A XYZ 12,000,000

A LLL 3,000,000

A MMM 700,000

A ZZZZ 10,750,000

B ABC 11,000,000

B ABC -

B XYZ 5,000,000

B LLL 3,000,000

B MMM 700,000

B ZZZZ 20,000,000
 
Welcome to the forums!


Will this array (Ctrl+Shift+Enter to confirm) formula work for you?

=LARGE(IF((A2:A13="A")*(B2:B13="ABC"),C2:C13),2)
 
Hi Luke, Thanks for responding. Unfortunately that will not work, because it assumes that I know in advance that 'ABC' is the 2nd Largest Value. The assumption that needs to be made is that I do not know what value of VALUE2 is the largest.
 
Hi Kchiba, Thanks for responding. You are correct, a pivot table does work, and it is the current solution.


However, the # of items which require this same type of solution is about to increase significantly for me, and I was seeking to prevent a workbook from being swallowed by sheet after sheet of pivot tables. If the message boards simply don't think that this can be accomplished with SUMPRODUCT, I would appreciate that advice as well.
 
Are you sure the answer is 11,500,000, I think it is 12,000,000 with A XYZ, the largest is B ZZZ 20,000,000
 
Yes. In the example I laid out, I want the 2nd largest item based on the sum of all items where Value 2 is the same, and where Value 1 = A. So that means we exclude anything with 'B' in the Value 1 column.
 
So, in the sample above, what I'm essentially trying to recreate is the logic that says


IF Value 1 = A, Value 2 is provided, but which one, when summed with other Value 2s that are the same is the 2nd largest is unknown.


Value 1 Value 2 Value 3

A ABC = 11,500,000 (the net of the two lines)

A XYZ = 12,000,000

A LLL = 3,000,000

A MMM = 700,000

A ZZZZ = 10,750,000


Hope I'm not muddying the waters too much.
 
More information required, what is known, do you know the unique values in VALUE1 and VALUE2 ?


Value 1 > Yes.


Value 2 > which one is used is dependent on which of the associated values in column Value 3 add up to.
 
just realized I used a '>' (greater than sign) to denote just an arrow in the previous post. it should just be a arrow/pointer.
 
You can achieve this with a macro that will create a table of unique Value1 & Value2 combinations and then find the 2nd Largest.


Will this work for you?
 
Yes, I am relatively strong on interpreting VB after the fact and then modifying it to fit my needs. Creating it from scratch, not so good.
 
Below is a macro to do what you need, please note that the original data is on Source and the calculation is on Target, so name the sheets accordingly.


I have not cleaned up the macro for moving data ranges yet, but you can try this and if you need help cleaning it up, I will help you with that.


I have to go now


good luck


Sub Cal2Large()


Sheets("Target").Select

Cells.Select

Selection.ClearContents


Sheets("Source").Select

Range("B2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Sheets("Target").Select

Range("B1").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("B1").Select

Selection.RemoveDuplicates Columns:=1, Header:=xlNo

' Range("B1").Select

Selection.End(xlDown).Select

Selection.Offset(2, 0).Select


Sheets("Source").Select

Range("C2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Sheets("Target").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Selection.RemoveDuplicates Columns:=1, Header:=xlNo


Selection.CurrentRegion.Select

Selection.Copy

Selection.End(xlDown).Select

Selection.Offset(1, 0).Select

ActiveSheet.Paste

Range("B1").Select

Application.CutCopyMode = False

Selection.Copy

Range("A4:A8").Select

ActiveSheet.Paste

Range("B2").Select

Application.CutCopyMode = False

Selection.Copy

Range("A9:A13").Select

ActiveSheet.Paste

Range("C4").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _

"=SUMIFS(Source!R2C4:R13C4,Source!R2C2:R13C2,Target!RC[-2],Source!R2C3:R13C3,Target!RC[-1])"

Range("C4").Select

Selection.AutoFill Destination:=Range("C4:C13")

Range("C1").Select

Selection.FormulaArray = "=LARGE(IF(R4C1:R13C1=RC[-1],R4C3:R13C3,0),2)"

Selection.Copy

Range("C2").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Selection.FormulaArray = "=LARGE(IF(R4C1:R13C1=RC[-1],R4C3:R13C3,0),2)"


End Sub
 
Kchiba,


Thank you immensely. I am out the next two days, but will report back on Monday how my tweaking it goes.


-Mark
 
Along the lines of a macro, here's a UDF that would give you more versatility.

[pre]
Code: 
Function GroupSums(V1 As Range, V2 As Range, V3 As Range, Criteria As String, N As Integer)
'V1 is the range you are checking criteria against
'V2 is the range your need to group
'V3 is the range to sum
'Criteria is the value you are looking for in V1
'N is the Nth largest value you want to find

Dim xCount As Integer, a as Integer, i as Integer
'Note: I just randomly picked 6000. Just needs to be a large enough number
Dim xArray(1 To 6000) As Variant
Dim yArray(1 To 6000) As Variant
Dim StoredCheck As Boolean

xCount = V2.Count
i = 0

For Each c In V1
i = i + 1
If c.Value = Criteria Then
StoredCheck = False
'Record previously found?
For a = 1 To xCount
If xArray(a) = V2.Cells(i, 1).Value Then
'Previous entry found, add value to log
yArray(a) = yArray(a) + V3.Cells(i, 1).Value
StoredCheck = True
Exit For
End If
Next a

'If not found, create new log entry
If StoredCheck <> True Then
xArray(i) = V2.Cells(i, 1).Value
yArray(i) = V3.Cells(i, 1).Value
End If
End If
Next c

'Find the Nth largest value from log
GroupSums = WorksheetFunction.Large(yArray(), N)

End Function
[/pre]
 
Luke, Chiba, thank you both very much. I ended up using Luke's code however. The only addition I would make, is to add:


Dim C as Variant


Once that was done, it worked perfectly.


Best

Mark
 
You must log in or register to reply here.
Back
Top