to sum cells on condition

[klnp_mysore]

Hi, Please any one can help me to find the sum of cells on condition and place it to a different cell

 

[MrKowz]

Check out the SUMIF formula.

 

[klnp_mysore]

Hi,
I have the macro, but I am not able to get the sum correctly
Sub Update()
Dim myrange As Range
Dim myrange1 As Range
Dim sum As Range
Dim c As Range
Dim d As Range
Dim total As Double
Set myrange1 = Range("O10:O1000")
Set myrange = Range("U11:U1000")
Set sum = Range("M3")
Set sum1 = Range("M3")
For Each c In myrange1
If c.Value = UCase("ywy(p)") Then
For Each d In myrange
total = total + d.Cells(i)
sum.Value = total / 1000
Next d
Else
If c.Value = UCase("ywy(p)-fr") Then
For Each d In myrange
total = total + d.Cells(i)
sum1.Value = total / 1000
Next d
End If
End If
Next c
End Sub

 

[MrKowz]

Given I understand your condition (If the value in column O = "ywy(p)" OR if the value = "ywy(p)-fr", then add the value from column U.) You actually don't need to loop at all.

VBA has the capabilities to use many worksheet functions within itself, including SUMIF:

Public Sub Update()
Dim myrange     As Range, _
    myrange1    As Range
    
Set myrange = Range("U11:U1000")
Set myrange1 = Range("O10:O1000")
Range("M3").Value = (Application.SumIf(myrange1, "ywy(p)", myrange) / 1000) + _
                    (Application.SumIf(myrange1, "ywy(p)-fr", myrange) / 1000)
End Sub

However, if you much prefer your version, this should work for you:

Sub Update()
Dim myrange As Range
Dim myrange1 As Range
Dim sum As Range
Dim c As Range
Dim d As Range
Dim total As Double
Set myrange1 = Range("O10:O1000")
Set sum = Range("M3")
For Each c In myrange1
    If c.Value = UCase("ywy(p)") Or c.Value = UCase("ywy(p)-fr") Then
        total = total + c.Offset(0, 6).Value
    End If
Next c
sum.Value = total / 1000
End Sub

If this is not the case (or if this does not work for you), please try explaining what it is you want other than a vague question. The better details I have concerning your situation, the better answer I can give.

Also, when posting code, please wrap your code with [CODE] and [/CODE] tags, that way it retains indention and makes things much easier to debug and read.

 

[klnp_mysore]

Hi,
Thanks Kowz for replying. In the below code in IF condition only first part of the condition is working, the OR part of the condition is not working correctly. IF the column O is "ywy(p)" or "ywy(p) - fr" , i have to add all the values from the column U and place it to the cell M3. Like this I have another 3 condition like "2xwy(p)", "2xwy(p) -fr" , find the sum and update it to another column N3.

Dim myrange As Range
Dim myrange1 As Range
Dim sum As Range
Dim c As Range
Dim d As Range
Dim total As Double
Set myrange1 = Range("O10:O1000")
Set sum = Range("M3")
For Each c In myrange1
    If c.Value = UCase("ywy(p)") Or c.Value = UCase("ywy(p)-fr") Then
        total = total + c.Offset(0, 6).Value
    End If
Next c
sum.Value = total / 1000

 

[MrKowz]

Is it "ywy(p)-fr" or "ywy(p) - fr"? (Note the additional spaces in the second one.

 

[klnp_mysore]

Yes there is spaces
"ywy(p) - fr"
Is there any way to auto format, to remove the spaces even though user enters spaces.

 

[MrKowz]

Yes there is spaces
"ywy(p) - fr"
Is there any way to auto format, to remove the spaces even though user enters spaces.

Yes, this will require an additional macro. For this particular macro, do not place it in a module. Instead, right click on the tab of the worksheet you want it to run on, and click "view code". Paste this code into that window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O:O")) Is Nothing Then
    Target.Value = Application.Substitute(Target.Value, " ", "")
End If
End Sub

This code will look for when you enter data into column O. When you enter data, it will automatically remove ALL spaces from the value entered.

 

[klnp_mysore]

Public Sub Update()
Dim myrange     As Range, _
    myrange1    As Range
    
Set myrange = Range("U11:U1000")
Set myrange1 = Range("O10:O1000")
Range("M3").Value = (Application.SumIf(myrange1, "ywy(p)", myrange) / 1000) + _
                    (Application.SumIf(myrange1, "ywy(p)-fr", myrange) / 1000)
End Sub

Hi,
Mr Kowz

suppose if new condition arise in column U , each time I have to add it in the code. Is there any code that will automatically take the new condition and find the corresponding sum.
eg. suppose if I add "2xwy(p)" in column the program should automatically take it for condition and find the sum.

 

[MrKowz]

How would Excel know what is a new condition and what things aren't? (ie. why would 2xwy(p) be a new condition and how would Excel know to treat it as such?)