How to write the "Sum" function

Excelboo2

Board Regular
Joined
Oct 11, 2006
Messages
175
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Gary Drumm

Active Member
Joined
Feb 22, 2005
Messages
462
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

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

Excelboo2

Board Regular
Joined
Oct 11, 2006
Messages
175
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
Oct 11, 2006
Messages
175

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

Code:
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.
 

mcubert

New Member
Joined
May 28, 2008
Messages
1
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.

Code:
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.
 

Forum statistics

Threads
1,141,281
Messages
5,705,474
Members
421,397
Latest member
JBenn911

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top