SUMIF in VBA

bhanu2003

Board Regular
Joined
Dec 9, 2003
Messages
76
Hi,

tried searching to see if i could avoid the post but, sorry could not find, so posting.

i want a variable to have the value of a sumif condition.

That is, say

SUMACT = SUMIF(E28:I28,"",E27:I27)

Thanks,
Bhanu :rolleyes:
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

bhanu2003

Board Regular
Joined
Dec 9, 2003
Messages
76
Hi Juan,

Thanks,

the same formula works in the sheet but not in VBA and always returns a '0'.

Any help.

Thanks,
Bhanu
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416

ADVERTISEMENT

Juan’s macro snippet works for me:
Code:
Sub Macro1()

Dim Sumact As Range
Set Sumact = Sheets(1).Range("A1")

Sumact = Application.SumIf(Range("E28:I28"), "", Range("E27:I27"))

End Sub
Alternative:

Code:
Sub Macro2()
Dim Sumact As Range
Set Sumact = Sheets(1).Range("A1")

With Sumact
  .Formula = "=SUMIF(E28:I28,"""",E27:I27)"
  .Value = .Value
End With

End Sub
Note:
See the second macro. When you enter a worksheet function directly into VBA, you need a double "" to denote a blank.

Regards,

Mike
 

bhanu2003

Board Regular
Joined
Dec 9, 2003
Messages
76
Hi,

Can any one please let me know if i am doing something wrong here.

i donot want a cell to have the value as i would be closing without changes rather i want a code variable to directly "get" the value afte doing a SUMIF.

or is your way to do it, the only possible one.

Thanks,
Bhanu :rolleyes:
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Simply re-define the variable Sumact --

Dim Sumact as Double
Sumact = ...
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Not sure what you mean when you say that you want a code variable to directly "get" the value after doing a SUMIF.

A message box perhaps?
Code:
Sub Macro3()
Dim Sumact As Long

Sumact = Application.SumIf(Range("E28:I28"), "", Range("E27:I27"))

MsgBox Sumact
' or do something else with the Sumact value?

End Sub

HTH

Mike
 

bhanu2003

Board Regular
Joined
Dec 9, 2003
Messages
76
Sorry for the confusion, i could not get my HTML maker to ork earlier, now here is what i want to do, i know it is simple but i am in a tangle some where, Thank you for your patience.
Forecast Sheet V1.9 - 2-23-04.xls
EFGHIJ
250
260
27002019367280
28657680221
290
30
31
Forecast - Hours


i need,

Sumact = 260 because it is the sum of cells E27:I27 where E28:I28 has no value.

Thanks,
Bhanu
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
The below does not work?

Dim Sumact as Double
' do stuff
Sumact = Application.SumIf(Range("E28:I28"), "", Range("E27:I27"))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,095
Messages
5,768,054
Members
425,451
Latest member
JohnBrooksBiddle

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
Top