Home

Adding Nth Cells

J4_ck
New Contributor

Hello, Im pretty new to excel, can anyone give me a hand?

 

Im trying to add up every 3rd cell in my table. (Looking at cell E3, addind up "product focus sales" for each week. for each person)

 

I've tried using this - " =SUM(E6:E110*(MOD(ROW(E6:E110),3)=0))"  It has returned a #Value Error

 

I have also tried using this - "=SUMPRODUCT(E6:E110,--(MOD(ROW(E6:E110)-ROW(E6)+1,3)=0))" And it returns "0.034740044" which is completly incorrect.

 

Heres the link to my document, If you dont mind having a look, that'll help me out a lot!

https://1drv.ms/x/s!AuXosbokesOhng0EwYby8j_zdrWJ

2 Replies

Hi,

 

With SUM you shall use array formula (Ctrl+Shift+Enter), but SUMPRODUCT returns correct result. 0.0347 that's just 3.47% which is correctly sums 0.3% in E14, and 3.17% in E17, all the rest are zero.

 

Please check attached.

The 2nd formula is correct. Your aim is to add every 3rd row starting with the 8th row. Thus, you must adjust the row numbers to become divisible by 3.
By subtracting 5, row numbers 6:110 were converted to 1:105. You can also achieve the same result by subtracting 2 or by adding 1 to the row numbers, as follows:
1. Subtract 2: =SUMPRODUCT(E6:E110,
--(MOD(ROW(E6:E110)-2,3)=0)); or
2. Add 1: =SUMPRODUCT(E6:E110,
--(MOD(ROW(E6:E110)+1,3)=0))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies