Forum Discussion
Adding Nth Cells
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!
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))
