Adding column range increments by 4 if dragged to the right

R

rye_sniper

New Member
Joined
Apr 10, 2014
Messages
7
Hello Experts:

Below is the complex table I am working on.

JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
SP200701RNF Rainfall (mm)SP200702RNF Rainfall (mm)SP200703RNF Rainfall (mm)SP200704RNF Rainfall (mm)SP200705RNF Rainfall (mm)SP200706RNF Rainfall (mm)SP200707RNF Rainfall (mm)SP200708RNF Rainfall (mm)SP200709RNF Rainfall (mm)SP200710RNF Rainfall (mm)SP200711RNF Rainfall (mm)SP200712RNF Rainfall (mm)
100:32,0.3100:15,0100:15,0100:54,0100:54,0100:30,0100:05,0100:05,0100:54,0100:17,0100:17,0100:35,0
101:32,0101:15,0101:15,0101:54,0101:54,0101:30,0101:05,0101:05,0.3101:54,0101:17,0101:17,0101:35,0
102:32,0.8102:15,0102:15,0102:54,0102:54,0102:30,0102:05,0102:05,0102:54,0102:17,0102:17,0102:35,0
103:32,0.3103:15,0103:15,0103:54,0103:54,0103:30,0103:05,0103:05,0103:54,0103:17,0103:17,0103:35,0
104:32,1104:15,0104:15,0104:54,0104:54,0104:30,0104:05,0104:05,0104:54,0104:17,0104:17,0104:35,0
105:32,0105:15,0105:15,0105:54,0105:54,0.5105:30,0105:05,0105:05,0105:54,0105:17,0105:17,0105:35,0
106:32,0106:15,0106:15,0106:54,0106:54,0106:30,0106:05,0.5106:05,0106:54,0106:17,0106:17,0106:35,0
107:32,0107:15,0107:15,0107:54,0107:54,0107:30,0107:05,0.3107:05,0107:54,0107:17,0107:17,0107:35,0
108:32,0108:15,0108:15,0108:54,0108:54,0108:30,0108:05,0108:05,0108:54,0108:17,0108:17,0108:35,0
109:32,0109:15,0109:15,0109:54,0109:54,0.3109:30,0109:05,0109:05,0109:54,0109:17,0109:17,0109:35,0
110:32,0110:15,0110:15,0110:54,0110:54,0110:30,0110:05,0110:05,0110:54,0110:17,0110:17,0110:35,0
111:32,0111:15,0111:15,0111:54,0111:54,0111:30,0111:05,0111:05,0111:54,0111:17,0111:17,0111:35,0
112:32,0112:15,0112:15,0112:54,0112:54,0112:30,0112:05,0112:05,0112:54,0112:17,0112:17,0112:35,0
113:32,0113:15,0113:15,0113:54,0113:54,0113:30,0113:05,0113:05,0113:54,0113:17,0113:17,0113:35,0
114:32,0114:15,0114:15,0114:54,0114:54,0114:30,0114:05,0114:05,0114:54,0114:17,0114:17,0114:35,0
115:32,0115:15,0115:15,0115:54,0115:54,0115:30,0115:05,1115:05,0115:54,0115:17,0115:17,0115:35,0
116:32,0116:15,1.8116:15,0116:54,0116:54,0116:30,0116:05,0.3116:05,0.3116:54,0116:17,0116:17,0116:35,0
117:32,0117:15,1117:15,0117:54,0117:54,0117:30,0117:05,0.3117:05,0.5117:54,0117:17,0117:17,0117:35,0
118:32,0118:15,0118:15,0118:54,0118:54,0118:30,0118:05,0.5118:05,0.5118:54,0118:17,0118:17,0118:35,0
119:32,0119:15,0119:15,0119:54,0119:54,0119:30,0119:05,0119:05,0119:54,0119:17,0119:17,0119:35,0
120:32,0120:15,0.3120:15,0120:54,0120:54,0120:30,0120:05,0120:05,0120:54,0120:17,0120:17,0120:35,0
121:32,0121:15,0121:15,0121:54,0121:54,0121:30,0121:05,0121:05,0121:54,0121:17,0121:17,0121:35,0
122:32,0122:15,0122:15,0122:54,0122:54,0122:30,0122:05,0122:05,0122:54,0122:17,0122:17,0122:35,0
123:32,0123:15,0123:15,0123:54,0123:54,0123:30,0123:05,0123:05,0123:54,0123:17,0123:17,0123:35,0
2.43.1
200:32,0200:15,0200:15,0200:54,0200:54,0200:30,0200:05,0200:05,0200:54,0200:17,0200:17,0200:35,0
201:32,0201:15,0201:15,0201:54,0201:54,0201:30,0201:05,0201:05,0201:54,0201:17,0201:17,0201:35,0
202:32,0202:15,0202:15,0202:54,0202:54,0202:30,0202:05,0202:05,0202:54,0202:17,0202:17,0202:35,0
203:32,0203:15,0203:15,0203:54,0203:54,0203:30,0203:05,0203:05,0203:54,0203:17,0203:17,0203:35,0
204:32,0204:15,0204:15,0204:54,0204:54,0204:30,0204:05,0204:05,0204:54,0204:17,0204:17,0204:35,0
205:32,0205:15,0205:15,0205:54,0205:54,0205:30,0205:05,0205:05,0205:54,0205:17,0205:17,0205:35,0
206:32,0206:15,0206:15,0206:54,0206:54,0206:30,0206:05,0206:05,0206:54,0206:17,0206:17,0206:35,0
207:32,0207:15,0207:15,0207:54,0207:54,0207:30,0207:05,0207:05,0207:54,0207:17,0207:17,0207:35,0
208:32,0208:15,0208:15,0208:54,0208:54,0208:30,0208:05,0208:05,0208:54,0208:17,0208:17,0208:35,0
209:32,0209:15,0209:15,0209:54,0209:54,0209:30,0209:05,0209:05,0209:54,0209:17,0209:17,0209:35,0
210:32,0210:15,0210:15,0210:54,0210:54,0210:30,0210:05,0210:05,0210:54,0210:17,0210:17,0210:35,0
211:32,0211:15,0211:15,0211:54,0211:54,0211:30,0211:05,0211:05,0211:54,0211:17,0211:17,0211:35,0
212:32,0212:15,0.3212:15,0212:54,0212:54,0212:30,0212:05,0212:05,0212:54,0212:17,0212:17,0212:35,0
213:32,0213:15,0213:15,0213:54,0213:54,0213:30,0213:05,0213:05,0213:54,0213:17,0213:17,0213:35,0
214:32,0214:15,0214:15,0214:54,0214:54,0214:30,0214:05,0214:05,8.4214:54,0214:17,0214:17,0214:35,0
215:32,0215:15,0215:15,0215:54,0215:54,0215:30,0215:05,0215:05,8.9215:54,0215:17,0215:17,0215:35,0
216:32,0216:15,0216:15,0216:54,0216:54,0216:30,0216:05,0216:05,0216:54,0216:17,0216:17,0216:35,0
217:32,0217:15,0217:15,0217:54,0217:30,0217:30,0217:05,0217:05,0217:54,0217:17,0217:17,0217:35,0
218:32,0218:15,0218:15,0218:54,0218:30,0218:30,0218:05,0218:05,0218:54,0218:17,0218:17,0218:35,0
219:32,0219:15,0219:15,0219:54,0219:30,0219:30,0219:05,0219:05,0219:54,0219:17,0219:17,0219:35,0
220:32,0220:15,0220:15,0220:54,0220:30,0220:30,0220:05,0220:05,0220:54,0220:17,0220:17,0220:35,0
221:32,0221:15,0221:15,0221:54,0221:30,0221:30,0221:05,0221:05,0221:54,0221:17,0221:17,0221:35,0
222:32,0222:15,0222:15,0222:54,0222:30,0222:30,0222:05,0222:05,0222:54,0222:17,0222:17,0222:35,0
223:32,0223:15,0223:15,0223:54,0223:30,0223:30,0223:05,0223:05,0223:54,0223:17,0223:17,0223:35,0

<tbody>
</tbody>


I already identified how I will summarize it per "SUM of RNF" per "SP" (Where RP = Date in the Calendar) according to Month using this formula:

=SUM(IF($A$5:$A$1002=$AZ5,C$5:C$1002,0))

The result is this:

JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemeber
12.43.138.40
200.300
303.810
4000.30
50004.7
60.31.900
70.3000
886.13.800
94.30.600
101107.90
113.400.30
12003.30
135.2004.8
143.100.30.5
15000.324.6
160000
1703.200
1800.300
190000
200000
210005.4
222.10110.4
23004.81.5
240000
250000
260000
270005.8
286.1000.6
2910.220.100
300000
310.30058.3

<tbody>
</tbody>

However, my problem is that "RNF"s are 4 columns apart and what I am doing now is modifying the value of cell range every time I change to each month. Is it possible for us to do that. I tried some functions like Offset and Index but no luck.

Thanks!

Best Regards
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Try something like the below

=SUMIF(INDIRECT(ADDRESS(1,COLUMN(B$1)*4-3,4,1)&":"&ADDRESS(1000,COLUMN(B$1)*4-3,4,1)),$AZ5, INDIRECT(ADDRESS(1,COLUMN(B$1)*4-1,4,1)&":"&ADDRESS(1000,COLUMN(B$1)*4-1,4,1)))


where INDIRECT(ADDRESS(1,COLUMN(B$1)*4-3,4,1)&":"&ADDRESS(1000,COLUMN(B$1)*4-3,4,1)) will become

A1:A1000 and

where INDIRECT(ADDRESS(1,COLUMN(B$1)*4-1,4,1)&":"&ADDRESS(1000,COLUMN(B$1)*4-1,4,1)) will become

C1:C1000



If you drag it right 1 column, A1:A1000 becomes E1:E1000 as the column is calculate by

COLUMN(B$1)*4-3

so (2 * 4) - 3 = 5 which is column E
 
Upvote 0
Sir:

Thank you very much! That solved it! You are great! Still way to go for me to learn!

Best Regards,

Ryan
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
132
bebo021999
B
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
350
awoohaw
awoohaw
N
  • Question
Need Formula help
Replies
5
Views
398
AhoyNC
A
A
  • Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
221
Aadya
A
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
417
Fluff
Fluff

Forum statistics

Threads
1,216,175
Messages
6,129,311
Members
449,499
Latest member
HockeyBoi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top