CODEWITHSUNDEEP
excelvba
Marc
Marc

Reputation: 25

Sum The values if same text

I have a list where in column AD I have numbers and in AE I have the values. in Column AG I have the numbers but with no duplicates. Id like to sum the values in column AH when there same number. 

100453504   1       
100453504   2   
100453504   3           
100453504   4           
100453504   5           
100453504   6           
100453504   7           
100453504   8           
100453627   9           
100453627   10          
100453627   11  

100453504   36      
100453627   30

I set up the ranges but I dont know how to operate with two of them. 

Dim Projects As Range
Projects = Range("Ad5", Range("Ad5").End(xlDown))

Dim Sortedprojects As Range
Sortedprojects = Range("Ag5", Range("Ag5").End(xlDown))

For Each cell In Projects

If cell.Value = Sortedprojects.Cells.Value Then

Upvotes: 0

Views: 1046

Answers (2)

OverflowStacker
OverflowStacker

Reputation: 1348

I would also suggest using a formula in such a simple case. If you have a list of all occuring numbers, you can put:

=SUMPRODUCT((A1:A14=D2)*(B1:B14))

behind the number for which you want to sum up the other values. If you dont have a list of all single numbers without doubles, you can use this: ARRAY FORMULA: CTRL + SHIFT + ENTER

=IFERROR(INDEX(A:A,MATCH(1,(COUNTIF(D$1:D1,A$1:A$99)=0)*(A$1:A$99<>""),0)),"")

Pull down. This produces a list without doubles. The cell above the first fomula has to be empty or with a heading.

enter image description here

Upvotes: 1

Mikku
Mikku

Reputation: 6654

Like this:

enter image description here

Use the Formula:=SUMIF(A1:B14,D1,B1:B14) , set the Ranges accordingly

If you still wanna go with Macro:

Dim Projects As Range
Set Projects = Range("AD5", Range("AD5").End(xlDown))

Dim Sortedprojects As Range
Set Sortedprojects = Range("AG5", Range("AG5").End(xlDown))

Dim cel As Range, cel1 As Range, Sm As Integer

For Each cel In Sortedprojects

Sm = 0

    For Each cel1 In Projects

         If cel.Value = cel1.Value Then
            Sm = Sm + cel1.Offset(0, 1).Value
         End If
    Next

    cel.Offset(0, 1).Value = Sm

Next

We are looping through the Non-duplicate Range, and summing over the Duplicate range if condition is met.

I will suggest you to Use SumIf

Upvotes: 3

Related Questions