Sum Every Nth Row in Excel
HTML-код
- Опубликовано: 10 янв 2014
- You may need to sum the value in every nth row in a list. This formula sums the value in every fifth row. The SUMPRODUCT function has been used along with ROW and MOD for the criteria.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
thanks man, you're truly a live saver.
This was a massive help for some of my high school students. Your explanation was crisp and to the point. Massive thanks.
You're very welcome Nathan.
This worked wonders!!! Excellent explanations of each function, great job my man!
Great to hear! Thank you Shayne.
Worked first time. Made my day! Thanx
Awesome, thanks William.
THANK YOU A MILLION TIMES
You're very welcome Shadi.
Worked liked a charm, thank you kindly
You're very welcome 👍
excellent. what about the column, I mean how to do the same function if its all column wise?
Thank you sir!
My pleasure.
Thank you so much!
You're welcome, Harini.
How do I then drag this along? So that new rows follow same function?
Finally I found this video. I wanted to add every nth row.
Awesome! Happy to help.
hi sir the formula is working only if numbers on the row , on my case formula calculate every 6th row but if i add some text on row 3 or 4 it is giving value error ? is there any way to fix this ?
Thanks mate.
You're welcome, Engin 👍
This is very helpful! I was wondering how can we calculate the rows in between? Example would be every 4th value of the 5th row. I tried adding -1 after the array2 but does not work, only add the value of every 4th row not every 4th value of 5th row (=SUMPRODUCT((MOD(COLUMN('Daily COR Percentage'!$N$5:$BC$5)-13,6)=0)*('Daily COR Percentage'!$N$5:$BC$5)))
want to to know ho to select the Nth row ie D5:P5 where N is a random number in column A
This is exactly what I needed to do, but I got the wrong answer. My total starts on the 10th row then I have to add every 13th row thereafter. It is 13 because there are 12 rows in between, one for each month of the year. The formula is inserted on the 9th row right above the first total on row 10. What did I do wrong?
Can you help me calculate the total sum from the sum-product formula?
S.NO EXPENSES HEAD Bank 1 Bank 2 Bank 3 Bank 4 Bank 1 Bank 2 Bank 3 Bank 4 Bank 1 Bank 2 Bank 3 Bank 4
I need total of BANK 1 by sumproduct formula
thanks for your help, and i needed one more solution, whenever the nth cell has a number, i am able to use the formula you have prescribed, but when the nth cell is a result of a calculation, it shows value error, can you please shed some light on this topic. thanks
The result may be formatted as text. This should not be a problem.
Nice :)
However, what if there are irregularities in the column. Foe example i need sum of every totlas that are not exactly in every fifth row??
This would probably be a case for SUMIF and potentially using some wildcards depending on the exact scenario. We would need a way of identifying each row.
How would you tell excel to give you the summed numbers for each city (sum B3:B6, then B8:B11, etc.)
They are already in this spreadsheet in cells 7, 12 etc. So to get those we could use a similar technique to the video as there is a pattern to their appearance (every 5th row).
However if they were not there, and it was just a list of the sales from each city. I would use a PivotTable. It is the quickest and simplest way.
Check out this guide on how to use PivotTables - ruclips.net/video/Bt4DfNldTmE/видео.html
Thank you!
Your welcome.
What about empty cells, that can be located between? This formula doesn't work for them.
No, this formula is just for a specific every nth row. So just for data with that pattern.
A formula can be adapted for the situation you need though.
this won't work for me. I want to add every 6th row. Please elaborate
The formula and technique used in the video can be adapted for other situations. Use a 6 instead of a 5 when it tells you to in the video.
Technique is correct. I am using index value i am adding a wide range of certain cells. My formula =SUMPRODUCT((MOD(ROW($F$5:$F$442),5)=0)*($F$5:$F$442)).
Hi, sorry, you lost me at 7:55. How are we calculating what number to minus?
I subtracted 2 because I have two rows above my dataset. The data starts in row 3 of the spreadsheet, which I need to make row 1.
That makes sense. I think I was just all numbered out after being on excel for hours. I figured it out by trial and error in the end, but thank you for getting back to me on this.@@Computergaga
Why is this program so shitty, this is not working btw.
It works, please try again.