How to write the "Sum" function

[Excelboo2]

I know excel already has a buildin "sum" function. But since I want to customize the "sum" function so anyone can show me how to write the "sum" by our own? Thanks

 

[Gary Drumm]

Hello, Welcome to the board,

I'm not sure I understand totally, but here is what I do.

Go to the cell where you want the sum to appear, then in the formula bar, just enter =sum(A4:A24)

(substitute your range, I used A4:A24 as an example only.

Gary

 

[Scott Huish]

Hi, welcome to the board!

Can you provide some details about what you are trying to do exactly?

 

[Excelboo2]

thanks for your quick respond. What I really want to do is to write a sum function by macro:

Function Selfdefine_Sum()

end function

 

[SydneyGeek]

To add everything in A2:A20, you would type =SUM(A2:A20)

To add every 4th row, you could use =SUM(A4,A8,A12,A16,A20)

To add A4 and A10:A20, you would use =SUM(A4,A10:A20)

So...
If the range has contiguous cells, use a colon to separate the first and last cells in the range.
If the cells don't join up, use commas to separate the chunks. You can use colons for groups of cells as well (see third example).

SUM will accept up to 27 commas. The following functions use exactly the same syntax:
MAX
MIN
COUNT
COUNTA
AVERAGE
STDEV
STDEVP

Denis

 

[SydneyGeek]

On second thoughts, it sounds like you want to write a custom function -- the other replies came through while I was compiling the first one).

How exactly do you need to customise SUM? What features do you want, that it currently lacks? The reason I ask, is that SUM is fast. Any custom function that you create in VBA will be slower, so you need a compelling reason to change the inbuilt functions

Denis

 

[Excelboo2]

On second thoughts, it sounds like you want to write a custom function -- the other replies came through while I was compiling the first one).

How exactly do you need to customise SUM? What features do you want, that it currently lacks? The reason I ask, is that SUM is fast. Any custom function that you create in VBA will be slower, so you need a compelling reason to change the inbuilt functions

Denis

Thanks, since my requirement is keep changing, so I want to learn how to write the "sum" function then customise it on a later date. I don't mind it will be slower since it must be faster than I use the build-in sum function directly anyway, I guess.

 

[Scott Huish]

Well, this is a basic sum function, but depending on what your future needs are, it may be better to write code for those needs.

Function sum2(r)
Dim c, d
For Each c In r
    If IsNumeric(c) Then d = d + c
Next c
sum2 = d
End Function

This isn't an exact duplication of the SUM function, if you want to supply numbers to this function rather than a range, pass them to the function as an array.

 

[Excelboo2]

it's amazing. thank you so much

 

[mcubert]

Hi, I have a similar question. I need to pass a cell reference to the sum function. Is this possible? My application:

I have a parts list for a machine. Some identical parts are used in different assemblies of the machine. I have a list sorted by part. i need to consolidate the identical parts and sum the total requirement for each part. for example:

Qty Part#
2 000001
3 000001
36 000001
24 567893
25 638362

i want to sum all the quantities for part 000001. can't really use a sumif because i have thousands of parts, unless someone can figure out a clever way to detect how many 000001's there are, and only sumif for that number of 000001's.

Thanks.

Well, this is a basic sum function, but depending on what your future needs are, it may be better to write code for those needs.

Function sum2(r)
Dim c, d
For Each c In r
    If IsNumeric(c) Then d = d + c
Next c
sum2 = d
End Function

This isn't an exact duplication of the SUM function, if you want to supply numbers to this function rather than a range, pass them to the function as an array.