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 !!!

 

[Shaikh Aziz]

okay , thankyou so much fluff.

Dear Fluff,

I am sorry, I am not so much used to with array function(Lbound & Ubound) as of currently I am intermediate student in VBA,

Will you please manipulate columns according to my attachment columns, for the below given coding by you,

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

I want to manipulate column as
columnA = ColumnD (4)
ColumnB = ColumnN (14)
ColumnC = ColumnO (15)

Sorry, and thankyou ?

 

[Fluff]

I specifically asked if you still wanted to look at columns A,B & C, to which you said yes.
Why on earth did you say yes if you want to look at columns D, N & O?

 

[Shaikh Aziz]

I specifically asked if you still wanted to look at columns A,B & C, to which you said yes.
Why on earth did you say yes if you want to look at columns D, N & O?

Dear Fluff,

I am sorry, I wasn't expected that you will writing a code through array's functions, and i already said that i created that sample just for understanding purpose, so if someone will suggesting me any code i can manipulate columns by myself. but i am not much used to with array's functions. hence i failed to manipulate columns in this situation.

Thankyou, once again i extremely sorry, please help me to re-arrange columns as per D, N & O. ?

 

[Fluff]

In future if somebody asks you a question, it is best if you give the true answer, rather than trying to simplify you requirements. Otherwise you will get something that doesn't work & waste members time.
How about

Sub ShaikhAziz()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, Rw As Long
   
   With Sheets("Sheet1")
      Ary = .Range("D2:N" & .Range("D" & 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, 11)
         Else
            Rw = .Item(Ary(r, 1))
            Nary(Rw, 1) = Nary(Rw, 1) + Ary(r, 11)
         End If
      Next r
   End With
   Sheets("sheet1").Range("O2").Resize(UBound(Nary)).Value = Nary
End Sub

 

[Shaikh Aziz]

In future if somebody asks you a question, it is best if you give the true answer, rather than trying to simplify you requirements. Otherwise you will get something that doesn't work & waste members time.
How about

Sub ShaikhAziz()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, Rw As Long
  
   With Sheets("Sheet1")
      Ary = .Range("D2:N" & .Range("D" & 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, 11)
         Else
            Rw = .Item(Ary(r, 1))
            Nary(Rw, 1) = Nary(Rw, 1) + Ary(r, 11)
         End If
      Next r
   End With
   Sheets("sheet1").Range("O2").Resize(UBound(Nary)).Value = Nary
End Sub

Dear Fluff,

Thankyou so much, and i got it in future i will post with exact column and rows, once again for justifying myself i thought i could manipulate the codes but i was wrong. and also i will not post questions to twice times, i thought it was open forum.

Thankyou so much for co-operating with me.?

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Shaikh Aziz]

Glad to help & thanks for the feedback.

Fluff, Hiiii

we got stuck again with another file but query was some just little changes would be there,
at this time we have to match 2 column criteria,

ColumC	ColumnD	ColumnN	ColumnO
Kharif-2019	Maharashtra	5​
Kharif-2019	Maharashtra	10​
Kharif-2019	Maharashtra	10​
Rabi-2019-20	Maharashtra	30​
Rabi-2019-20	Maharashtra	40​
Rabi-2019-20	Maharashtra	50​
Rabi-2019-20	Maharashtra	60​
Kharif-2019	Gujarat	50​
Kharif-2019	Gujarat	70​
Rabi-2019-20	Gujarat	80​
Rabi-2019-20	Gujarat	120​
Kharif-2019	Odisha	20​
Kharif-2019	Odisha	130​
Rabi-2019-20	Odisha	87​
Rabi-2019-20	Odisha	54​
Rabi-2019-20	Odisha	90​

this time we have to matching two columns ColumnC & ColumnD and ColumnO will get sumup and result would come out in ColumnN.

i think this time i posted correctly with all exact columns number, above is just for understanding purpose, this one is another file which i got today,

Please help !!!! you are only my saver.....?

Thankyou.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: summing up value based on another column criteria
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Shaikh Aziz]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: summing up value based on another column criteria
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

The way you shouted on me yesterday on my mistake i got scared hence when i found another forum i posted there too, but no one is answering properly they way you answered in short codes. see this is all my first time posting and all where i am seeking for an answer other wise i have to try codings based on my search and manipulating by myself. but after your answer and method of Lbound and Ubound i found wrong myself, hence before posting to you i posted in another forum.

 

[Fluff]

You still need to tell us that you have posted to others sites. I suggest you read this to understand why. Excelguru Help Site - A message to forum cross posters