# SUMIF in VBA

This is a discussion on SUMIF in VBA within the Excel Questions forums, part of the Question Forums category; Hi, tried searching to see if i could avoid the post but, sorry could not find, so posting. i want ...

1. ## SUMIF in VBA

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

2. ## Re: SUMIF in VBA

Try with

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

3. ## Re: SUMIF in VBA

Hi Juan,

Thanks,

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

Any help.

Thanks,
Bhanu

Any help !

5. ## Re: SUMIF in VBA

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

6. ## Re: SUMIF in VBA

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

7. ## Re: SUMIF in VBA

Simply re-define the variable Sumact --

Dim Sumact as Double
Sumact = ...

8. ## Re: SUMIF in VBA

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

9. ## Re: SUMIF in VBA

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.

******** ******************** ************************************************************************>
 Microsoft Excel - Forecast Sheet V1.9 - 2-23-04.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 J25J26E27F27G27H27I27J27J28J29 =

E
F
G
H
I
J
25
*****0
26
*****0
27
002019367280
28
657680**221
29
*****0
30
******
31
******
 Forecast - Hours *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

i need,

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

Thanks,
Bhanu

10. ## Re: SUMIF in VBA

The below does not work?

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

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•