Sum column based on criteria vba

[Shaikh Aziz]

Please find below samples, where I want to do coding for rest of my actual data set,
ColumnA has multiple states name, ColumnB has unique values, and in columnC we want a sumif of columnB & Merging cells results based on states name (columnA).

ColumnA	ColumnB	ColumnC
Maharashtra	5​	25
Maharashtra	10​
Maharashtra	10​
Gujarat	30​	180
Gujarat	40​
Gujarat	50​
Gujarat	60​
Odisha	80​	313
Odisha	50​
Odisha	70​
Odisha	68​
Odisha	45​

Our result would be come in columnC after sum of ColumnB with mergecells based on columnA.

I have tried below codings,

Dim ColumnA as long
Dim ColumnC as long

ColumnsC = 0
ColumnA = Worksheets("Sheet1").Cells(2, 1).value

Lastrow = Worksheets("sheet1").cells(Rows.Count, 1).End(xlUp).Row

For i = 2 to lastrow
IF Worksheets("Sheet1").Cells(i, 1).value = ColumnA Then
ColumnC = ColumnC + Worksheets("Sheet1").cells(i, 3).value
End if
Next

worksheets("sheet1").cells(2, 3).value = ColumnC

But it's calcuating for columnA (first state only).

Please help me to finding perfect coding.
Thankyou !!!

 

[Fluff]

Hi & welcome to MrExcel.
Why not just put this formula in col C?

+Fluff v2.xlsm
	A	B	C
1	ColumnA	ColumnB	ColumnC
2	Maharashtra	5	25
3	Maharashtra	10
4	Maharashtra	10
5	Gujarat	30	180
6	Gujarat	40
7	Gujarat	50
8	Gujarat	60
9	Odisha	80	313
10	Odisha	50
11	Odisha	70
12	Odisha	68
13	Odisha	45
Master

Cell Formulas
Range	Formula
C2:C13	C2	=IF(A2<>A1,SUMIFS(B:B,A:A,A2),"")

 

[Shaikh Aziz]

Dear Fluff,

I have huge data around with 96,000 rows and 58 columns, that was just a part of it for understanding purpose, I need to append that code into my actual data set code file. That's why I need macro coding.

Thanks

 

[Fluff]

Are you still looking at cols A & B with the output in C?

 

[Shaikh Aziz]

Are you still looking at cols A & B with the output in C?

Yes. Based on ColA, ColB values should get sum and output would be in ColmnC with mergeCells based on ColA.

 

[Fluff]

Ok, how about

Sub ShaikhAziz()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, Rw As Long
   
   With Sheets("Sheet1")
      Ary = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .exists(Ary(r, 1)) Then
            .Add Ary(r, 1), r
            Nary(r, 1) = Ary(r, 2)
         Else
            Rw = .Item(Ary(r, 1))
            Nary(Rw, 1) = Nary(Rw, 1) + Ary(r, 2)
         End If
      Next r
   End With
   Sheets("sheet1").Range("C2").Resize(UBound(Nary)).Value = Nary
End Sub

 

[Shaikh Aziz]

Ok, how about

Sub ShaikhAziz()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, Rw As Long
  
   With Sheets("Sheet1")
      Ary = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .exists(Ary(r, 1)) Then
            .Add Ary(r, 1), r
            Nary(r, 1) = Ary(r, 2)
         Else
            Rw = .Item(Ary(r, 1))
            Nary(Rw, 1) = Nary(Rw, 1) + Ary(r, 2)
         End If
      Next r
   End With
   Sheets("sheet1").Range("C2").Resize(UBound(Nary)).Value = Nary
End Sub

Thankyou so much, But Output is not getting merged into ColumnC based on ColumnA.

 

[Fluff]

If you mean you want to use merged cells DON'T, they are an abomination & should be avoided like the plague.

 

[Shaikh Aziz]

If you mean you want to use merged cells DON'T, they are an abomination & should be avoided like the plague.

okay , thankyou so much fluff.

 

[Fluff]

You're welcome & thanks for the feedback.