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

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

Microsoft Excel

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
How about this formula, assuming your data is in range A1:F1

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

Thomas

=SUMPRODUCT(ISEVEN(COLUMN(

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.

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.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Thanks NeillZ - works a treat!

Just a quick idea, use Cell instead of ActiveCell.