I have an old lotus 123 spreadsheet (a budget) that used the @nsum function.
It allowed me to sum every third or forth column. The text below shows the
syntax. My question... does excel have a function like this?

Adds every nth value in list, starting at offset.
Syntax
@NSUM returns the sum of (offset), (offset + n), (offset + 2n), (offset +
3n), ... in list. @NSUM(0;1;list) returns the same result as @SUM(list).
Example
@NSUM(1;3;B5..B15) returns the sum of the values in B6, B9, B12 and B15.

Thanks in advance,

mh

Change the ,4 to suit your variable

=SUMPRODUCT(A2:F2,--(MOD(COLUMN(A2:F2),4)=1))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"marke54805" wrote in message
...
I have an old lotus 123 spreadsheet (a budget) that used the @nsum
function.
It allowed me to sum every third or forth column. The text below shows
the
syntax. My question... does excel have a function like this?

Adds every nth value in list, starting at offset.
Syntax
@NSUM returns the sum of (offset), (offset + n), (offset + 2n), (offset +
3n), ... in list. @NSUM(0;1;list) returns the same result as @SUM(list).
Example
@NSUM(1;3;B5..B15) returns the sum of the values in B6, B9, B12 and B15.

Thanks in advance,

mh

No it doesn't, you can however use other functions

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1),2)=0),A1:A100)


will sum cell A1, A3, A5 etc


=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1),3)=0),A1:A100)

will sum

A1, A4, A7 etc so every third row


There are many nice function in Lotus that are better than in Excel




--


Regards,


Peo Sjoblom

"marke54805" wrote in message
...
I have an old lotus 123 spreadsheet (a budget) that used the @nsum
function.
It allowed me to sum every third or forth column. The text below shows
the
syntax. My question... does excel have a function like this?

Adds every nth value in list, starting at offset.
Syntax
@NSUM returns the sum of (offset), (offset + n), (offset + 2n), (offset +
3n), ... in list. @NSUM(0;1;list) returns the same result as @SUM(list).
Example
@NSUM(1;3;B5..B15) returns the sum of the values in B6, B9, B12 and B15.

Thanks in advance,

mh

Thank you kindly, Don and Peo!

I agree, Peo, Lotus has many functions I'd prefer to use.



"marke54805" wrote:

I have an old lotus 123 spreadsheet (a budget) that used the @nsum function.
It allowed me to sum every third or forth column. The text below shows the
syntax. My question... does excel have a function like this?

Adds every nth value in list, starting at offset.
Syntax
@NSUM returns the sum of (offset), (offset + n), (offset + 2n), (offset +
3n), ... in list. @NSUM(0;1;list) returns the same result as @SUM(list).
Example
@NSUM(1;3;B5..B15) returns the sum of the values in B6, B9, B12 and B15.

Thanks in advance,

mh

If you want something more "friendly" looking that the other solutions
posted so far, you can create a User Defined Function to duplicate the
functionality of the NSUM function. From a worksheet, press Alt+F11 to get
into the VBA editor and select Insert/Module from its menu bar, then
copy/paste the following code into the code window that opened when you did
that...

Function NSUM(Offset As Long, SkipAmount As Long, Rng As Range) As Double
Dim X As Long
With Rng
If .Rows.Count 1 And .Columns.Count 1 Then
MsgBox "This function only works with single rows or single " & _
"columns!", vbCritical, "Improper Range Specified"
End If
If .Rows.Count 1 Then
For X = 1 + Offset To .Rows.Count Step SkipAmount
NSUM = NSUM + .Cells(X).Value
Next
Else
For X = 1 + Offset To .Columns.Count Step SkipAmount
NSUM = NSUM + .Cells(X).Value
Next
End If
End With
End Function

Now, go back to the worksheet and enter this into a cell...

=NSUM(1,3,B5:B15)

and it should produce the sum that you are looking for.

--
Rick (MVP - Excel)


"marke54805" wrote in message
...
I have an old lotus 123 spreadsheet (a budget) that used the @nsum
function.
It allowed me to sum every third or forth column. The text below shows
the
syntax. My question... does excel have a function like this?

Adds every nth value in list, starting at offset.
Syntax
@NSUM returns the sum of (offset), (offset + n), (offset + 2n), (offset +
3n), ... in list. @NSUM(0;1;list) returns the same result as @SUM(list).
Example
@NSUM(1;3;B5..B15) returns the sum of the values in B6, B9, B12 and B15.

Thanks in advance,

mh

Not a direct replacement for your NSUM, but you can get the answer from
=SUMPRODUCT((B6:B15)*(MOD(ROW(B6:B15),3)=0))
You could include the OFFSET function too, if you wanted to.
--
David Biddulph


"marke54805" wrote in message
...
I have an old lotus 123 spreadsheet (a budget) that used the @nsum
function.
It allowed me to sum every third or forth column. The text below shows
the
syntax. My question... does excel have a function like this?

Adds every nth value in list, starting at offset.
Syntax
@NSUM returns the sum of (offset), (offset + n), (offset + 2n), (offset +
3n), ... in list. @NSUM(0;1;list) returns the same result as @SUM(list).
Example
@NSUM(1;3;B5..B15) returns the sum of the values in B6, B9, B12 and B15.

Thanks in advance,

mh

"Rick Rothstein" wrote...
If you want something more "friendly" looking that the other solutions
....

And A LOT SLOWER if used frequently!

Function NSUM(Offset As Long, SkipAmount As Long, Rng As Range) As Double
* Dim X As Long
* With Rng
* * If .Rows.Count 1 And .Columns.Count 1 Then
* * * MsgBox "This function only works with single rows or single " & _
* * * * * * *"columns!", vbCritical, "Improper Range Specified"
* * End If
....

A classic and stupendous design MISTAKE!

NEVER have udfs display @#$% error dialogs! The problem is filling or
pasting formulas calling a udf into perhaps thousands of cells, but
for one reason or another, the filled or pasted formulas trigger the
error. Rather than Excel's recalc engine QUICKLY returning error
values, Excel would instead display the same STUPID error dialog for
EVERY cell that triggers the error.

For someone who supposedly knows something about VB, you don't seem to
have much practical experience with VBA udfs.

Then there's the question why this should be an error. It's useful to
know the particulars of the OP's question, which in this case means
having a 123 help file handy if not an actual installed 123 version. I
have both, and 123's @NSUM handles 3D(!!) ranges as 3rd argument. For
example, the 123 formula

@NSUM(0,3,A:C3..D:F11)

returns a numeric result, not an error. 123 iterates through its 3rd
argument in the same order as it would iterate through 3D arguments to
@NPV, which defaults to by row then by column then by sheet. Simple
enough to come up with a 2D equivalent udf, even extend it to handle
multiple area ranges. Note that 123 iterates through ranges by row
then by column, so For Each iteration through Excel ranges would
usually produce different results.


Function nsum(ByVal offs As Long, n As Long, a As Variant) As Variant
Dim x As Variant, k As Long, i As Long, j As Long, m As Long

k = -offs - 1

If offs < 0 Or n < 1 Then 'trap 123 error conditions and return
error values QUICKLY!
nsum = CVErr(xlErrNum)

ElseIf TypeOf a Is Range Then
For m = 1 To a.Areas.Count
For j = 1 To a.Areas(m).Columns.Count
For i = 1 To a.Areas(m).Rows.Count
k = k + 1
If k = 0 And k Mod n = 0 _
And VarType(a.Areas(m).Cells(i, j).Value2) = vbDouble Then
_
nsum = nsum + a.Areas(m).Cells(i, j).Value2
Next i
Next j
Next m

ElseIf IsArray(a) Then
For Each x In a
k = k + 1
If k = 0 And k Mod n = 0 And VarType(x) = vbDouble Then _
nsum = nsum + x
Next x

ElseIf offs = 0 And n = 1 Then 'return the same result as 123 for
degenerate ranges
nsum = a

End If

End Function


This still leaves support for variable number of arguments. You wanna
try that?

Yes, UDF's are slower, and maybe I should have mentioned that on the
off-chance the OP was not aware of this fact; but the point of my posting
was that I thought the UDF interface would be more familiar for the OP to
work with than the less obvious SUMPRODUCT formulas that were offered.

Yes, including the error message was a foolish last minute add in on my part
(done spur of the moment without having given it much thought). As for my
experience... no, it does not involve a lot of UDF development. If you
remember me at all from when you first "jumped on my case" with some
frequency about two and a half years ago when I began volunteering in the
Excel newsgroups, then you might remember that the largest part of my
programming experience was with the compiled version of VB, not VBA... and
in that environment, error messages like the one I used here made more
sense. As an aside, it might have been more beneficial to the thread if you
had simply chosen to point out the error in my approach without including
all the attendant histrionics. In any event, I appreciate, as I always have,
the benefit of your wisdom as contained in your underlying comments.

I never used Lotus 123, so I was not familiar with the particulars of its
NSUM function; rather, I relied on the OP's posted syntax which did not
suggest that a 3D component existed for this function. I have a question,
though... does the Lotus 123 NSUM function actually wrap the areas as your
code does? By that I mean, for this formula...

=NSUM(1,3,B5:C15)

the cells that will be added are B6, B9, B12, C7, C10, C13... notice the
Column B cells are offset, row-wise, from the Column A cells. Of course this
will happen whenever the loop through the rows does not finish on the
column's last processed row. Not having any personal experience with the
actual NSUM function, this looks, at first blush, to be somewhat useless to
me... my gut feeling tells me it would be more practical to have the same
row processed in each column

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
"Rick Rothstein" wrote...
If you want something more "friendly" looking that the other solutions
....

And A LOT SLOWER if used frequently!

Function NSUM(Offset As Long, SkipAmount As Long, Rng As Range) As Double
Dim X As Long
With Rng
If .Rows.Count 1 And .Columns.Count 1 Then
MsgBox "This function only works with single rows or single " & _
"columns!", vbCritical, "Improper Range Specified"
End If
....

A classic and stupendous design MISTAKE!

NEVER have udfs display @#$% error dialogs! The problem is filling or
pasting formulas calling a udf into perhaps thousands of cells, but
for one reason or another, the filled or pasted formulas trigger the
error. Rather than Excel's recalc engine QUICKLY returning error
values, Excel would instead display the same STUPID error dialog for
EVERY cell that triggers the error.

For someone who supposedly knows something about VB, you don't seem to
have much practical experience with VBA udfs.

Then there's the question why this should be an error. It's useful to
know the particulars of the OP's question, which in this case means
having a 123 help file handy if not an actual installed 123 version. I
have both, and 123's @NSUM handles 3D(!!) ranges as 3rd argument. For
example, the 123 formula

@NSUM(0,3,A:C3..D:F11)

returns a numeric result, not an error. 123 iterates through its 3rd
argument in the same order as it would iterate through 3D arguments to
@NPV, which defaults to by row then by column then by sheet. Simple
enough to come up with a 2D equivalent udf, even extend it to handle
multiple area ranges. Note that 123 iterates through ranges by row
then by column, so For Each iteration through Excel ranges would
usually produce different results.


Function nsum(ByVal offs As Long, n As Long, a As Variant) As Variant
Dim x As Variant, k As Long, i As Long, j As Long, m As Long

k = -offs - 1

If offs < 0 Or n < 1 Then 'trap 123 error conditions and return
error values QUICKLY!
nsum = CVErr(xlErrNum)

ElseIf TypeOf a Is Range Then
For m = 1 To a.Areas.Count
For j = 1 To a.Areas(m).Columns.Count
For i = 1 To a.Areas(m).Rows.Count
k = k + 1
If k = 0 And k Mod n = 0 _
And VarType(a.Areas(m).Cells(i, j).Value2) = vbDouble Then
_
nsum = nsum + a.Areas(m).Cells(i, j).Value2
Next i
Next j
Next m

ElseIf IsArray(a) Then
For Each x In a
k = k + 1
If k = 0 And k Mod n = 0 And VarType(x) = vbDouble Then _
nsum = nsum + x
Next x

ElseIf offs = 0 And n = 1 Then 'return the same result as 123 for
degenerate ranges
nsum = a

End If

End Function


This still leaves support for variable number of arguments. You wanna
try that?

the cells that will be added are B6, B9, B12, C7, C10, C13... notice the
Column B cells are offset, row-wise, from the Column A cells.

....the Column B cells are offset, row-wise, from the **Column C** cells...


--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
"Rick Rothstein" wrote...
If you want something more "friendly" looking that the other solutions
...

And A LOT SLOWER if used frequently!

Function NSUM(Offset As Long, SkipAmount As Long, Rng As Range) As Double
Dim X As Long
With Rng
If .Rows.Count 1 And .Columns.Count 1 Then
MsgBox "This function only works with single rows or single " & _
"columns!", vbCritical, "Improper Range Specified"
End If
...

A classic and stupendous design MISTAKE!

NEVER have udfs display @#$% error dialogs! The problem is filling or
pasting formulas calling a udf into perhaps thousands of cells, but
for one reason or another, the filled or pasted formulas trigger the
error. Rather than Excel's recalc engine QUICKLY returning error
values, Excel would instead display the same STUPID error dialog for
EVERY cell that triggers the error.

For someone who supposedly knows something about VB, you don't seem to
have much practical experience with VBA udfs.

Then there's the question why this should be an error. It's useful to
know the particulars of the OP's question, which in this case means
having a 123 help file handy if not an actual installed 123 version. I
have both, and 123's @NSUM handles 3D(!!) ranges as 3rd argument. For
example, the 123 formula

@NSUM(0,3,A:C3..D:F11)

returns a numeric result, not an error. 123 iterates through its 3rd
argument in the same order as it would iterate through 3D arguments to
@NPV, which defaults to by row then by column then by sheet. Simple
enough to come up with a 2D equivalent udf, even extend it to handle
multiple area ranges. Note that 123 iterates through ranges by row
then by column, so For Each iteration through Excel ranges would
usually produce different results.


Function nsum(ByVal offs As Long, n As Long, a As Variant) As Variant
Dim x As Variant, k As Long, i As Long, j As Long, m As Long

k = -offs - 1

If offs < 0 Or n < 1 Then 'trap 123 error conditions and return
error values QUICKLY!
nsum = CVErr(xlErrNum)

ElseIf TypeOf a Is Range Then
For m = 1 To a.Areas.Count
For j = 1 To a.Areas(m).Columns.Count
For i = 1 To a.Areas(m).Rows.Count
k = k + 1
If k = 0 And k Mod n = 0 _
And VarType(a.Areas(m).Cells(i, j).Value2) = vbDouble Then
_
nsum = nsum + a.Areas(m).Cells(i, j).Value2
Next i
Next j
Next m

ElseIf IsArray(a) Then
For Each x In a
k = k + 1
If k = 0 And k Mod n = 0 And VarType(x) = vbDouble Then _
nsum = nsum + x
Next x

ElseIf offs = 0 And n = 1 Then 'return the same result as 123 for
degenerate ranges
nsum = a

End If

End Function


This still leaves support for variable number of arguments. You wanna
try that?

"Rick Rothstein" wrote...
....
I never used Lotus 123, so I was not familiar with the particulars of its
NSUM function; . . .

If I do nothing else than convince people who've never used 123 to
exercise just a little bit or reticence about responding to 123-to-
Excel posts, I'll have done something positive.

. . . does the Lotus 123 NSUM function actually wrap the areas as your
code does? By that I mean, for this formula...

=NSUM(1,3,B5:C15)

the cells that will be added are B6, B9, B12, C7, C10, C13... notice the
Column B cells are offset, row-wise, from the Column A cells. Of course this
will happen whenever the loop through the rows does not finish on the
column's last processed row. Not having any personal experience with the
actual NSUM function, this looks, at first blush, to be somewhat useless to
me... my gut feeling tells me it would be more practical to have the same
row processed in each column

Simple enough to test. In 123R8 I enter the following values in C3..D7

1 600000
20 7000000
300 80000000
4000 900000000
50000 0.5

The 123 formula @NSUM(1,3,C3..D7) returns 80050020, or, in order,
C4+C7+D5. I enter the same values in C3:D7 in Excel, and using my nsum
udf, the formula =nsum(1,3,C3:D7) also returns 80050020. This follows
123's default iteration order through C3:D7, namely, C3, C4, C5, C6,
C7, D3, D4, D5, D6, D7, so offsetting 1 and including every 3rd item
means including C4, C7, D5.

Whether this is useful or not is up to the user, but if the goal is
presumably giving the OP a udf that doesn't FUBAR any nontrivial
formula porting from 123 to Excel, then you have to live with the 123
specs whether or not they make sense to you. If you don't know the 123
specs and/or can't figure out how to find them using a web search, why
respond with a udf that (LIKELY!) wouldn't be robust?

I never used Lotus 123, so I was not familiar with the particulars of its
NSUM function; . . .

If I do nothing else than convince people who've never used 123 to
exercise just a little bit or reticence about responding to 123-to-
Excel posts, I'll have done something positive.

I hope this is not some kind of life-goal for you because, if it is, I'm
afraid I'm going to be disappointing you if a similar situation should come
up in the future. First, let me agree, to some extent, with the underlying
sentiment in your comment though. When I posted this back to the OP...

"If you want something more "friendly" looking that the other solutions
posted so far, you can create a User Defined Function to duplicate the
functionality of the NSUM function..."

I will admit that it overstated what I was providing. Rather than saying the
code duplicated the functionality of the NSUM function and stopping, I
should have continued the above quote with something like "... as you have
portrayed it to us in your supplied example." I believe part of the reason
the VBA side of Excel exists is to allow a user to create (or have created
for them) a method of providing an easy-to-use interface to Excel for some
given task that is expected to be repeated. If the OP is not all that
familiar with SUMPRODUCTs, then he might experience some problems in trying
to reuse the formulas provided to him for other similar situations... the
VBA function I provided would allow him a more familiar (read as, more
easily reusable) interface to use for other similiar situations he might
have reason to make use it for. Did my function implement **all** of Lotus
123's NSUM function? No, of course not, but I don't believe it was necessary
for it to do so (whether I was aware of all the parts of Lotus 123's
function or not)... all it had to do is implement the functionality the OP
said he was looking for. Providing a full replacement function might be a
nice exercise (and for some others out there, even a desireable one),
however, for the question that was asked, I believe doing so would be
overkill and might, because of the extra code and different manner of
construction, possibly slow down the execution further over that of the code
I provided while offering no extra usable functionality to the OP.

--
Rick (MVP - Excel)


. . . does the Lotus 123 NSUM function actually wrap the areas as your
code does? By that I mean, for this formula...

=NSUM(1,3,B5:C15)

the cells that will be added are B6, B9, B12, C7, C10, C13... notice the
Column B cells are offset, row-wise, from the Column A cells. Of course
this
will happen whenever the loop through the rows does not finish on the
column's last processed row. Not having any personal experience with the
actual NSUM function, this looks, at first blush, to be somewhat useless
to
me... my gut feeling tells me it would be more practical to have the same
row processed in each column

Simple enough to test. In 123R8 I enter the following values in C3..D7

1 600000
20 7000000
300 80000000
4000 900000000
50000 0.5

The 123 formula @NSUM(1,3,C3..D7) returns 80050020, or, in order,
C4+C7+D5. I enter the same values in C3:D7 in Excel, and using my nsum
udf, the formula =nsum(1,3,C3:D7) also returns 80050020. This follows
123's default iteration order through C3:D7, namely, C3, C4, C5, C6,
C7, D3, D4, D5, D6, D7, so offsetting 1 and including every 3rd item
means including C4, C7, D5.

Whether this is useful or not is up to the user, but if the goal is
presumably giving the OP a udf that doesn't FUBAR any nontrivial
formula porting from 123 to Excel, then you have to live with the 123
specs whether or not they make sense to you. If you don't know the 123
specs and/or can't figure out how to find them using a web search, why
respond with a udf that (LIKELY!) wouldn't be robust?