Link to home
Create AccountLog in
Avatar of matt_m
matt_m

asked on

VBA Function for a Sumif

Hi All,

I have been asked at work to produce a formula which will do a sumif on a range in a single row where the criteria are in adjacent cells.

For example 20,1, 10,1,15,2

The result would sumif if the cell to the right is 1 - giving an answer of 30 in the above array.

I am struggling to produce a formula that can do this so I have gone to a custom function.  What I have  is below (which doesn't work)


Public Function MattsSumIF(Criteria As Double, NRange as range)

Dim Cell As Range
Total = 0
i = 1
For Each Cell In NRange
    If ActiveCell.Offset(0, i).Value = Criteria Then
    Total = ActiveCell.Value + Total
    End If
   
        i = i + 1
       
Next Cell
   
    MattsSumIF = Total
   
   
End Function

Any ideas?

Thanks
Matt
Avatar of Norie
Norie

Matt

Just a quick idea, use Cell instead of ActiveCell.
ASKER CERTIFIED SOLUTION
Avatar of NeillZ
NeillZ
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
How about this formula, assuming your data is in range A1:F1

=SUMPRODUCT(ISEVEN(COLUMN(B1:F1))*(B1:F1=1)*A1:E1)

Thomas
Additional notes:
1. Bad editing note:
You don't need to Dim Cell (left in when editing your function)
Should read: Dim Total As Double, i As Long

2. Error handling:
If the data is always correct (i.e. what you are summing is numbers) the above will always work. Typo's resulting in Alphas in the sum cells will return #VALUE error (which you probably  want in order to highlight data errors). The criteria cells can be alpha-numeric...

If you use the standard Excel array I provided, it will sum the result and ignore alpha’s (like a standard SUM does). If you want to use your function but achieve this result (i.e. ignoring alphas), Insert "On error resume next" as the first line in the code after the Dim.
Avatar of matt_m

ASKER

Thanks NeillZ  - works a treat!