Sum if / Group by VBA Module

[lpw0806]

Hi All,

I have a module written to pull data from one sheet (Raw Data) and into another (Opportunities). But I am having trouble with the last piece. I THINK I need to group by Column B in Raw Data - but I still want to sum by Column C in Raw Data.

Not sure if that is correct, but I was able to create what I wanted in a pivot table (example pasted below). I am trying to figure out how to apply this to my module. Any help or insight would be much appreciated!

Thank you!!

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0ff]Raw Data info for reference:[/COLOR]
Account Name = Column B
Opportunity Name = Column C
R Type = Column M
CM Prod = Column Q


[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0cd]Pivot table creation:[/COLOR]

Sum of CM PROD		R Type
Account Name	Opportunity Name	Carryover	New	Renewal
Account 123	Opportunity 123			$218,446.00
Account 123b	Opportunity 123	$81,548.00
Account 125	Opportunity 125			$91,536.00
Account 126	Opportunity 126	$242,450.00
Account 127	Opportunity 127	$43,630.00
Account 128	Opportunity 128		$62,120.00
Account 129	Opportunity 129			$105,682.00

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0ff]Module currently built:[/COLOR]

Sub MoveDataToOpportunitiesSheet()
Dim rowCount, count, destinationCount, probability As Integer
Dim selectedPOD, rowPOD, rType As String
Set sourceSheet = ActiveWorkbook.Sheets("Raw Data")
Set currentSheet = ActiveWorkbook.Sheets("Opportunities")
' Clear Data from Sheet
currentSheet.Rows("6:" & currentSheet.Rows.count).ClearContents
' get the total rows present
rowCount = sourceSheet.UsedRange.Row + sourceSheet.UsedRange.Rows.count - 1
count = 2
destinationCount = 6
selectedPOD = cmbPOD2.Value
For i = count To rowCount
probability = sourceSheet.Cells(i, 11).Value
rowPOD = sourceSheet.Cells(i, 19).Value
rType = sourceSheet.Cells(i, 13).Value
If probability >= 75 And probability <= 100 And rowPOD = selectedPOD Then
currentSheet.Cells(destinationCount, 1).Value = sourceSheet.Cells(i, 2).Value
currentSheet.Cells(destinationCount, 2).Value = sourceSheet.Cells(i, 3).Value
currentSheet.Cells(destinationCount, 3).Value = sourceSheet.Cells(i, 1).Value
currentSheet.Cells(destinationCount, 4).Value = sourceSheet.Cells(i, 12).Value
If rType = "New" Then
currentSheet.Cells(destinationCount, 5).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 5).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 5).Value = ""
End If

If rType = "Renewal" Then
currentSheet.Cells(destinationCount, 6).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 6).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 6).Value = ""
End If

If rType = "Carryover" Then
currentSheet.Cells(destinationCount, 7).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 7).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 7).Value = ""
End If
destinationCount = destinationCount + 1
End If
Next i
Range("A6:G6").Sort key1:=Range("B6"), order1:=xlAscending, Header:=xlNo
Columns("A:G").Select
Selection.EntireColumn.AutoFit
End Sub

 

[lpw0806]

Hi All --

Is there someone who can help me answer this?

Thank you in advance!