Is there a function in Excel which will add values from every nth row in a data set?
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
Addition every nth row (Excel 2002 SP3)
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Addition every nth row (Excel 2002 SP3)
- This topic has 8 replies, 3 voices, and was last updated 16 years, 11 months ago.
Viewing 1 reply threadAuthorRepliesWSHansV
AskWoody Lounger-
WSAnneD
AskWoody Lounger -
WSAnneD
AskWoody Lounger -
WSHansV
AskWoody LoungerJune 4, 2008 at 4:26 pm #1111469The SUMPRODUCT function takes two (or more) arrays, multiplies the corresponding elements and adds the results. For example, SUMPRODUCT(A1:A3,B1:B3) is evaluated as A1*B1 + A2*B2 + A3*B3.
In the formula I posted, the two arrays are MOD(ROW(A1:A100),5)=2 and A1:A100. The latter is the column containing the values that you want to add.
ROW(A1:A100) returns the list of row numbers in A1:A100, i.e. 1, 2, 3, 4, 5, 6, 7, 8, …., 99, 100.
MOD(…,5) returns the remainder of the first argument after division by 5.
So MOD(ROW(A1:A100),5) returns 1, 2, 3, 4, 0, 1, 2, 3, …, 4, 0.
MOD(ROW(A1:A100),5)=2 returns a list of TRUE/FALSE values – TRUE if the remainder is 2, FALSE otherwise: FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, …
In calculations in Excel, FALSE is equivalent to 0 and TRUE is equivalent to 1, so we have 0, 1, 0, 0, 0, 0, 1, 0, …
As you see, every 5th element starting with the 2nd equals 1, all others are 0.
When we combine this with A1:A100 in SUMPRODUCT, only the cells in row 2, 7, 12 etc. contribute to the result because they are multiplied with 1, the others don’t because they are multiplied with 0.Clear as mud now?
-
WSAnneD
AskWoody Lounger -
WSTimOz
AskWoody LoungerJune 4, 2008 at 5:52 pm #1111487Same sleeve as the formula?
If Hans’ explanation needs further explanation you can also use the built-in Excel resources.
The “fx” (insert function) button on the formula bar (or Shift-F3) describes and allows you to select among available functions, and guides you through parameter entry, step by step. If the brief guidance in the dialog is insufficient, you can click on the “Help on this function” link (bottom left) to take you direct to function specific help.
What I often do to build up a complex composite of functions, as per Hans’ solution, is to work out each component in a separate cell before combining them. This process is especially useful to get my head around unfamiliar functions.
(If you only have single copy of your (precious) formula, make a copy before trying the following.)
The other trick I use is the F9 key for partial calculations (to debug formulae). If you select a part of the formula in the formula bar, and press F9, the selection is replaced by its calculated value. The selection can be anything from a single cell reference, to one, or more, functions. When you make the selection, limit cell references to to just the cell reference, and for functions include everything from the function name to the closing parenthesis. Repeated select/F9s let you “step” through a formula to see how the final result is calculated. You can use ESC to “back out” from the partial calculation(s) – ENTER will save the formula with the calculated values in place.If you’re really keen, you can always dive straight into the Excel help, but be prepared to guess the sometimes elusive, correct terminology (as with most help systems).
-
-
Viewing 1 reply thread -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
DBOS Advanced Network Analysis
by
Kathy Stevens
3 hours, 31 minutes ago -
Microsoft Edge Launching Automatically?
by
healeyinpa
31 minutes ago -
Google Chrome to block admin-level browser launches for better security
by
Alex5723
6 hours, 11 minutes ago -
iPhone SE2 Stolen Device Protection
by
Rick Corbett
6 hours, 53 minutes ago -
Some advice for managing my wireless internet gateway
by
LHiggins
3 hours, 22 minutes ago -
NO POWER IN KEYBOARD OR MOUSE
by
HE48AEEXX77WEN4Edbtm
23 minutes ago -
A CVE-MITRE-CISA-CNA Extravaganza
by
Nibbled To Death By Ducks
15 hours, 40 minutes ago -
Sometimes I wonder about these bots
by
Susan Bradley
11 hours, 58 minutes ago -
Does windows update component store “self heal”?
by
Mike Cross
2 hours ago -
Windows 11 Insider Preview build 27858 released to Canary
by
joep517
1 day, 5 hours ago -
Pwn2Own Berlin 2025: Day One Results
by
Alex5723
1 day, 5 hours ago -
Windows 10 might repeatedly display the BitLocker recovery screen at startup
by
Susan Bradley
2 hours, 16 minutes ago -
Windows 11 Insider Preview Build 22631.5409 (23H2) released to Release Preview
by
joep517
1 day, 8 hours ago -
Windows 10 Build 19045.5912 (22H2) to Release Preview Channel
by
joep517
1 day, 8 hours ago -
Kevin Beaumont on Microsoft Recall
by
Susan Bradley
20 hours, 55 minutes ago -
The Surface Laptop Studio 2 is no longer being manufactured
by
Alex5723
1 day, 16 hours ago -
0Patch, where to begin
by
cassel23
1 day, 10 hours ago -
CFPB Quietly Kills Rule to Shield Americans From Data Brokers
by
Alex5723
2 days, 6 hours ago -
89 million Steam account details just got leaked,
by
Alex5723
1 day, 17 hours ago -
KB5058405: Linux – Windows dual boot SBAT bug, resolved with May 2025 update
by
Alex5723
2 days, 14 hours ago -
A Validation (were one needed) of Prudent Patching
by
Nibbled To Death By Ducks
2 days, 5 hours ago -
Master Patch Listing for May 13, 2025
by
Susan Bradley
1 day, 16 hours ago -
Installer program can’t read my registry
by
Peobody
9 hours, 33 minutes ago -
How to keep Outlook (new) in off position for Windows 11
by
EspressoWillie
2 days, 3 hours ago -
Intel : CVE-2024-45332, CVE-2024-43420, CVE-2025-20623
by
Alex5723
2 days, 10 hours ago -
False error message from eMClient
by
WSSebastian42
3 days, 1 hour ago -
Awoke to a rebooted Mac (crashed?)
by
rebop2020
3 days, 10 hours ago -
Office 2021 Perpetual for Mac
by
rebop2020
3 days, 12 hours ago -
AutoSave is for Microsoft, not for you
by
Will Fastie
9 hours, 38 minutes ago -
Difface : Reconstruction of 3D Human Facial Images from DNA Sequence
by
Alex5723
3 days, 15 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.