We all know that we can write Formulas in Excel. for example, =Sum(A1:A10) to sum the values present in range from A1 to A10. The summation is easy. Right??? But what if we want to tweak our requirements.

Let's assume we have a requirement, that we need to sum only those values which are divisible by 2. I have assumed a very basic example, although we can write an Excel Built in for this requirement, but I want to show to achieve this through Custom Function.

For example, I want to sum range A1 to A10. I will write a custom Function in Excel VBA. Open VBA editor, add Module 1 and add a function like following :

`Public Function SumEvenValues(rng As Range) As Double`

Dim sum As Double

Dim cell_ As Range

sum = 0

For Each cell_ In rng.Cells

If (IsNumeric(cell_.Value)) Then

'the value is numeric. Check the mod

If (cell_.Value Mod 2 = 0) Then

sum = sum + cell_.Value

End If

End If

Next cell_

SumEvenValues = sum

End Function

In your excel workbook, in Cell b1, type this : =SumEvenValues(A1:A10)

You will see that all the values which are divisible by 2 are summed up and the result is displayed in Cell B1. I have taken a very basic example to show it's use, which can be easily achived in Excel, but we may encounter to include those requirements which we may not be able to achieve within Excel only. So in such a case, VBA is used to handle all those requirements which Excel cannot handle.

HTH,

Vikas

## 2 comments:

You can use an array formula for this:

{=SUM(IF(MOD(A1:A10,2)=0,A1:A10,0))}

Hi,

Oh yeah, we can write an array formula. That I already knew, and if you see, I have mentioned that you can write an Excel Built in formula for this requirement. But there may be some requirements, where we cannot realistically use the Excel Formula. I took this example not because we don't have any alternative, rather, this example is an easy to understand. I had intentionally opted for a simple example.

Thanks,

Vikas

Post a Comment