Microsoft Office Forums 5000 rows, want to select and perform addition on ever 48 rows
 Register FAQ Search Today's Posts Mark Forums Read

#1
06-09-2019, 10:09 PM
 Rexmaverick Windows 10 Office 2016 Novice Join Date: Jun 2019 Posts: 3
5000 rows, want to select and perform addition on ever 48 rows

Hi,

Thank you for having a look, I have done this years ago and cannot remember how to achieve it again,

As above I have approx 5000 rows, I wand to select O2:048 and put result in p48, then select O49:O97 and put result in P97, then O98:O145 and put result in P145.

Appreciate any assistance

Rex
#2
06-09-2019, 10:47 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 448

Would it be the case that all ranges are same number of rows, a relatively simple formula would do. As from your example follows, the 1st range is 47 rows, the 2nd one 49 rows, the 3rd one 48 rows, ... . So unless you have some other criterion in some column of your table allowing to decide, to which range/group of data the row belongs, and which you forgot to include in your message, there is no way to use formulas to get wanted result.

Either you provide some additional info, or you write a procedure which calculates and inserts needed result, and call it through shortcut after you select a range.
#3
06-09-2019, 11:03 PM
 Rexmaverick Windows 10 Office 2016 Novice Join Date: Jun 2019 Posts: 3

Hi yes same number of rows, it is a simple formula, I am trying to avoid copy and pasting 100 times.
#4
06-09-2019, 11:07 PM
 Rexmaverick Windows 10 Office 2016 Novice Join Date: Jun 2019 Posts: 3

Just found the old one, it is every 15 rows, =IF(MOD(C2,15)=0,E2,0) can drag this down and it does it.

Thanks anyway, always appreciated.
#5
06-10-2019, 01:19 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,383

Your formula is based on MOD(C2,..
Except if C2 contains the number of rows starting at the beginning, it won't work
Starting in O2, in P2 enter =IF(MOD(row(),49)=0,sum(\$O\$2:\$O2)-sum(\$P\$1:\$P1),"") and pull down
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
#6
06-10-2019, 06:29 AM
 p45cal Windows 10 Office 2016 Expert Join Date: Apr 2014 Posts: 257

Put your formula: =SUM( O2:O48) into cell P48.
Select cells P2:P48 as a single block.
Without de-selecting that range grab the drag-handle at the bottom left of cell P48 with the mouse and drag down as far as you need.
You may have to adjust this a bit because your first sum sums 47 rows, your second one 49 rows and your third 48 rows.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post bobk544 Word VBA 3 06-09-2018 05:24 AM Stc25234 Excel Programming 2 06-23-2017 10:51 AM LarryHills Excel 1 02-17-2017 03:55 PM Bathroth Word VBA 1 10-01-2014 01:40 PM dennist77 Word 1 10-29-2013 11:39 PM

All times are GMT -7. The time now is 10:28 AM.

 -- Default Style -- Lightweight Contact Us - Privacy Statement - Top