Easy VBA SumIf

BigNate

Board Regular
Joined
Dec 17, 2014
Messages
242
Hello Everyone,

How would I write the following formula in VBA (not using R1C1 notation)?

SUMIF(B5:B20,"<8000",F5:F20)

Thanks!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Most simply,

Rich (BB code):
mySum = WorksheetFunction.SumIf(Sheets("yoursheetname").Range("B5:B20"), "<8000", Sheets("yoursheetname").Range("F5:F20"))
Replace yoursheetname with the actual name of your WorkSheet.
 
Upvote 0
Most simply,

Rich (BB code):
mySum = WorksheetFunction.SumIf(Sheets("yoursheetname").Range("B5:B20"), "<8000", Sheets("yoursheetname").Range("F5:F20"))
Replace yoursheetname with the actual name of your WorkSheet.


Thanks! I'm trying to modify your example to fit my own spreadsheet, where the sum range and criteria range are dynamic. Unfortunately, I cannot quite seem to manipulate it just right. Do you think you could help me one more time by telling me what is wrong with the syntax of the line of my code with the sumif formula?

Code:
Sub sumiftest3()


Cells(1, 1).Value = "8900"


Dim TopEnd As Variant
    TopEnd = Application.Match(Cells(1, 1).Value, Worksheets("ABCDaily").Columns(2), 0)
    
Cells(Topend+2,5).value = Application.sumif(.Range(Cells(2,5),cells(Topend,5)),"8900",.Range(cells(5,5),cells(Topend,5))
End Sub

Thanks again!
 
Upvote 0
I'm not sure what sheets you are using so I am unsure how to respond. Are all values on the ABCDaily sheet? If not, what other sheet is there?

I can see the problem ultimately lies in the fact that you have ambiguous .Range functions, but you are also using Cells(x,y) without specifying a sheet either so I think even if I did give you something to use, without knowing where everything was coming from you might get some unexpected results.
 
Upvote 0
I tried using the ActiveSheet specification, but now it is giving me a 0. Any ideas? :)

I'm getting closer!
 
Upvote 0
ActiveSheet is the default if you don't specify any specific sheet, but I rarely trust that because sometimes the activesheet is not what you think unless the sub is being called from a button on that sheet.

I cannot test the following code, however, I believe this should work.

Code:
Sub sumiftest3()
Dim TopEnd As Variant
With Worksheets("ABCDaily")
    .Cells(1, 1).Value = "8900"
    TopEnd = Application.Match(.Cells(1, 1).Value, .Columns(2), 0)
    .Cells(TopEnd + 2, 5).Value = Application.SumIf(.Range(.Cells(2, 5), .Cells(TopEnd, 5)), "8900", .Range(.Cells(5, 5), .Cells(TopEnd, 5)))
End With
End Sub


If you are still getting 0 value after these changes, the next step would be to verify all of your cell ranges are evaluating to the expected lookup/return ranges you want.
 
Last edited:
Upvote 0
ActiveSheet is the default if you don't specify any specific sheet, but I rarely trust that because sometimes the activesheet is not what you think unless the sub is being called from a button on that sheet.

I cannot test the following code, however, I believe this should work.

Code:
Sub sumiftest3()
Dim TopEnd As Variant
With Worksheets("ABCDaily")
    .Cells(1, 1).Value = "8900"
    TopEnd = Application.Match(.Cells(1, 1).Value, .Columns(2), 0)
    .Cells(TopEnd + 2, 5).Value = Application.SumIf(.Range(.Cells(2, 5), .Cells(TopEnd, 5)), "8900", .Range(.Cells(5, 5), .Cells(TopEnd, 5)))
End With
End Sub


If you are still getting 0 value after these changes, the next step would be to verify all of your cell ranges are evaluating to the expected lookup/return ranges you want.

I really appreciate your help, but am returning a zero for the answer. Is there a way to modify so that the formula still shows? I thought I could achieve this by doing ".formula" instead of ".value" but that doesn't seem to do the trick. Also, can you help me to change the abover criteria to be >=8000? I think this will make it easier for me to see what is going on.

Thank you so much for your help!
 
Upvote 0
Also, can you help me to change the abover criteria to be >=8000? I think this will make it easier for me to see what is going on.

This actually I think explains the problem you are having... currently you are filtering the sumif to only sum where values are 8900 exactly
you should change the "8900" to "<=8900" or whatever range you are looking for.
 
Upvote 0
This actually I think explains the problem you are having... currently you are filtering the sumif to only sum where values are 8900 exactly
you should change the "8900" to "<=8900" or whatever range you are looking for.

BiocideJ, I appreciate your dedication to help me. Now however, I am getting a "type mismatch" error on the line with the sumif formula. Does anything look incorrect to you in regards to the syntax? I am completely stumped at this point.

Code:
Sub sumiftest4()
Dim TopEnd As Variant
With Worksheets("ABCDaily")
    .Cells(1, 1).Value = "8000"
    TopEnd = Application.Match(.Cells(1, 1).Value, .Columns(2), 0)
    .Cells(TopEnd + 2, 5).Value = Application.SumIf(.Range(.Cells(2, 5), .Cells(TopEnd, 5)), ">=8000", .Range(.Cells(5, 5), .Cells(TopEnd, 5)))
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top