OFFSET Function to make Excels SUM function Dynamic by theexcelclub

excel · @theexcelclub · Aug 31 '19

$4.60

OFFSET Function to make Excels SUM function Dynamic

Everyone can use the SUM function in Excel.  It is the most basic and used functions available. In fact, it is the only formula in Excel that has its own keyboard shortcut (Alt + =).  However many users find difficulty when adding additional rows to the data which they are summing.  The formula has taken cell references and this is causing problems.
<h2>OFFSET Function</h2>
To overcome this problem and make the SUM function moe dynamic we can use the OFFSET function. Eliminating the problem of adding additional rows of data to the data set that is to be totted.

We can use OFFSET to refer to the location of specific data in an Excel worksheet. It returns the value of a cell that is a specified number of rows and columns away from the active cell.

OFFSET is a reference function. The Syntax is

=OFFSET(Reference, Rows, Columns, [height],[width])

The OFFSET function will first look for a Reference, this is the starting point, the cell that you want the answer to end up in.  The function then looks for rows, and this is the number of rows away that you want to move to get the data you require.  If you are moving up rows you must use a minus value.  The function then looks for Columns, this is the number of columns away that you need to move to get the data you require. Height and width as they are placed inside [] mean that they are optional.

What we will look at in the video below is replacing cell references in the SUM function with the OFFSET function.  Now, this might sound a little complicated but to be honest, it's not.  This is an Excel Hack that every beginner Excel users should know!

Have a look at the video and it will explain all.  And if you find it useful I hope that you will like and share

<iframe src="https://www.youtube.com/embed/GjDYSU8Mdtk" width="560" height="315" frameborder="0" allowfullscreen="allowfullscreen"></iframe>

&nbsp;
<h3><center><strong><a href="https://theexcelclub.newzenler.com/courses/practical-beginner-excel-365">Looking for some FREE Excel Training?  Take Practical Beginner Excel Course FREE</a> </strong></center></h3>
<center><center><img class="aligncenter wp-image-1959 size-full" src="https://theexcelclub.com/wp-content/uploads/2016/09/beginner-excel.png" alt="Free online Beginner Excel Course" width="429" height="506" /></center><br/></center></h3>
<h4><center>In return for this Excel Tip –OFFSET Function to make the SUM function Dynamic – I ask that you share this post with your friends and colleagues</center></h4>
<h2><center><strong>Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox</strong> <strong>with Earn and Learn activities.</strong></center></h2>
<h2 class="wp-block-button aligncenter" style="text-align: center;"><a class="wp-block-button__link has-background has-vivid-cyan-blue-background-color steem-keychain-checked" href="https://theexcelclub.newzenler.com/f/email-signup">SIGN UP FOR NEWSLETTER NOW</a></h2>
<div class="wp-block-image" style="text-align: center;">
<figure class="aligncenter"><img class="wp-image-1604" src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?w=1170" sizes="(max-width: 225px) 100vw, 225px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?w=225&amp;ssl=1 225w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?resize=150%2C150&amp;ssl=1 150w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?resize=100%2C100&amp;ssl=1 100w" alt="learn and earn steem activity" /><br/></figure>
</div>
<p class="has-background has-vivid-green-cyan-background-color" style="text-align: center;"><strong>Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.</strong></p>
<p class="has-background has-vivid-green-cyan-background-color" style="text-align: center;"><strong>We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.</strong></p>

<center>To <a class="steem-keychain-checked" style="color: #0000ff;" href="https://theexcelclub.com/learn-and-earn-steem-activities/">Find out more now and start earning while you are learning Excel and Power BI</a></center></h3>

👍 steempress-io, krnel, newhope, alexis555, paulag, edkarnie, helpie, riosparada, solominer, trevonjb, theexcelclub, numberofthingz, abh12345, eonwarped, team, steempress, definethedollar, circa, dalz, truce, roncoejr, midlet, thelittlebank, gascap9000, likwid, ano123, taskmaster4450, supremebape, cryptovues, diverse, enforcer48, andrewramdas, ketcom, myotherstuff, mindtrap, kenanqhd, bitrocker2020, cryptoisa, theestateof.mind, gharrington44, daltono, ronaldoavelino, swisswitness, lagtvgames, topcryptonews, gandhibaba, abrockman, onepercentbetter, jluvs2fly, daan, marfonso, glastar, mmunited, coreyssteemit77, lagtv, michaeljn, blewitt, zainenn, accelerator, wrecktangle27, mytechtrail, mapxv, e-r-k-a-n, raj808, and 264 others

properties (23)vote details (328)

voter	rshares	pct
team	82,064,398,619	10%
holabebe	928,885,001	2.5%
mammasitta	253,271,470	0.05%
trevonjb	174,202,129,420	5%
daan	17,483,191,316	3%
sinzzer	2,743,886,167	5%
happyphoenix	2,405,550,445	5%
tensaix2j	654,842,327	5%
steempress	76,290,035,385	15%
riosparada	286,481,588,473	100%
anarcho-andrei	1,633,240,720	7.5%
jungleonion	5,677,597,456	5%
krnel	2,172,621,876,275	100%
mikehere	876,776,788	5%
abh12345	93,705,747,463	20%
rymlen	4,291,619,949	5%
newhope	1,465,004,346,779	16%
erikaflynn	7,881,999,060	22%
ejemai	7,821,737,835	100%
hattaarshavin	2,344,832,572	5%
omitaylor	76,129,131	2%
travelnepal	3,851,568,807	5%
alexis555	1,273,991,375,310	16%
flatearth	1,749,475,015	5%
lordneroo	2,568,161,258	5%
sames	2,419,334,706	5%
wilx	4,646,539,927	5%
sepracore	4,422,909,972	100%
linakay	2,272,640,973	5%
pwny	4,493,989,031	5%
thelordsfinest	2,511,950,426	5%
momogrow	4,234,647,681	5%
opinizeunltd	9,358,245,050	5%
billionairebit	1,641,362,390	5%
bitrocker2020	23,945,152,315	5%
reloadbeatbox	1,013,265,480	5%
christianolu	79,402,905	12.5%
greatvideos	3,030,798,363	5%
roncoejr	54,958,654,698	5%
hotsauceislethal	2,450,514,673	5%
moemanmoesly	1,961,505,711	5%
paulag	662,079,531,680	100%
kenanqhd	23,987,158,379	5%
jayna	3,324,899,880	3.75%
guchtere	1,123,982,254	12.5%
imredryan	296,331,103	5%
edkarnie	334,808,243,732	100%
dernan	227,697,997	50%
henryramos	1,550,247,963	5%
zorro7	1,013,325,791	5%
shitsignals	1,981,086,684	5%
amymya	117,353,776	2.5%
phelimint	2,916,151,430	5%
yoogyart	7,575,094,284	41%
dse	606,452,890	5%
jezsmith720	0	1%
krazypoet	315,472,079	2.5%
redrica	7,372,588,946	5.5%
syedumair	1,977,912,347	5%
otage	4,619,127,680	5%
andrewramdas	30,522,642,566	100%
zyx066	8,856,806,338	12.5%
aftabkhan10	2,649,213,859	5%
cflclosers	2,406,118,568	5%
aferrell	146,369,329	5%
djlethalskillz	125,672,782	0.2%
accelerator	14,804,753,016	0.47%
chimzycash	3,309,681,416	5%
raj808	11,326,308,416	5%
trevorpetrie	2,557,059,185	25%
hz432creations	96,978,575	12.5%
taskmaster4450	36,594,449,705	5%
storysharing	8,229,750,799	100%
infamousit	4,125,643,423	25%
landoftrading	2,052,355,002	5%
art-mess	327,909,177	12.5%
jaforce	11,283,373,232	5%
ekqwel22	1,912,207,198	5%
dbooster	1,879,797,671	5%
michaeljn	16,236,929,019	5%
socent	1,295,227,556	15%
coyotelation	442,325,986	10%
tworealsolutions	918,518,672	5%
eonwarped	86,017,195,212	9%
mattroconnor	1,355,911,826	5%
diverse	34,510,059,083	100%
wolfnworbeikood	6,090,938,687	13%
seanlloyd	184,973,815	1%
jssekhon	84,016,838	5%
potplucker	2,510,437,137	12.5%
fredkese	1,733,846,676	5%
evecab	759,313,962	1.25%
tute	1,508,814,611	100%
pechichemena	1,199,430,958	5%
infidel1258	9,808,806,084	5%
mytechtrail	14,415,941,832	25%
deepak0018	471,637,833	100%
kernelillo	362,255,817	12.5%
helpie	321,459,172,798	25%
syedubair	90,476,175	5%
markaustin	753,038,399	5%
luisferchav	993,285,410	12.5%
crypto-cody	279,457,268	5%
mitchadventures	3,247,369,290	5%
soulturtle	620,210,032	5%
californiacrypto	149,817,482	5%
daltono	21,719,559,508	5%
burlarj	5,911,111,493	5%
mountainjewel	1,214,539,700	0.5%
edwardstobia	87,530,575	1.25%
spiritualmax	1,933,208,460	5%
carpedimus	441,168,234	12.5%
gandhibaba	18,972,284,855	5%
bennettitalia	1,914,149,014	6.25%
turekivanoz	498,286,649	100%
kingeazi	435,933,263	50%
badmusgreene	3,465,394,912	5%
gascap9000	45,413,876,942	5%
richgang	274,409,592	5%
leoumesh	2,367,315,129	5%
edprivat	198,862,994	0.1%
lunaticpandora	7,328,353,431	5%
alinequeiroz	110,030,586	10%
cryptosharon	3,668,548,070	5%
hazem91	516,221,015	2.5%
numberofthingz	93,745,511,864	5%
udezee	2,670,921,794	5%
juandvg	205,359,982	12.5%
mindtrap	27,266,848,781	5%
misbitcap	520,658,015	5%
damajicman3	1,914,850,586	5%
cryptoslo	2,275,509,565	5%
zipporah	8,449,461,518	5%
makingthebest09	1,232,319,322	5%
randumb	2,794,758,795	5%
siomarasalmeron	2,564,205,750	17.5%
happyishadam	5,435,031,668	5%
wrecktangle27	14,662,260,109	5%
spawnband	545,974,635	5%
altcoinbandit	637,863,194	100%
oadissin	4,242,784,532	5%
limelightmg	5,050,043,692	5%
fturner19	2,436,880,751	5%
newenx	660,700,988	10%
rativiv	5,390,647,020	5%
avaava	2,633,726,307	5%
greendeliverence	1,984,169,437	5%
a0i	2,032,974,115	1%
truce	56,278,399,718	5%
onepercentbetter	18,944,279,314	5%
abrockman	18,954,159,229	5%
hypersonic1	3,941,083,947	5%
rawn	3,677,302,539	5%
mimidee74	1,003,000,811	5%
ronaldoavelino	21,634,457,733	5%
stmdev	90,525,724	2%
thomasbrown	5,958,654,594	5%
inlakech	479,350,628	25%
pg-live-pt	2,063,588,779	5%
theatreofdelays	333,894,609	100%
big-m	743,005,710	12.5%
pasie15	1,935,315,918	5%
midlet	53,609,269,920	5%
headcrypto	8,301,860,461	5%
tiket	1,892,138,258	5%
berien	804,065,236	15%
johhnnyturbo20	1,869,161,016	5%
steempress-io	5,804,906,471,534	15%
veckinon	676,387,243	12.5%
chinyerevivian	2,161,521,914	5%
lordgod	10,510,048,969	5%
solominer	181,046,238,802	25%
archaimusic	119,673,145	10%
rightwing670	8,062,963,644	5%
musicvoter	4,874,320,046	1%
kbr	580,042,213	6.25%
enforcer48	32,835,583,293	15%
free-reign	694,243,929	3.75%
misterro	102,377,893	25%
syedshakil	1,867,871,147	5%
shookriya	621,358,777	5.55%
frejafri	766,889,213	2.5%
blockurator	413,307,984	2.5%
lagtv	16,345,328,879	5%
chrstnv	1,799,923,145	5%
hmetu	2,133,595,148	5%
swisswitness	19,718,731,817	50%
marysemciver	261,574,326	10%
joelsegovia	1,811,710,812	5%
blewitt	15,222,576,697	1.75%
hermannsol	1,741,730,582	5%
salimbur	1,792,008,951	5%
tiffcisme	1,324,500,727	5%
emsonic	3,479,186,015	5%
w4d	0	5%
bflanagin	6,311,528,740	12.5%
melor9	69,373,197	12.5%
lillywilton	803,759,440	20%
preventsuicide	118,891,170	3.75%
ireenchew	1,956,402,906	5%
dalz	59,301,986,194	100%
zainenn	14,822,692,108	12.5%
jluvs2fly	18,595,873,554	5%
alexran	1,748,240,205	5%
lilcryptita	3,033,523,846	5%
cryptouno	239,807,934	5%
coiotes	822,365,936	10%
theexcelclub	152,704,066,130	100%
maeusenews	1,833,467,297	5%
hiatus	1,777,050,338	5%
thelittlebank	48,354,974,102	12.5%
definethedollar	73,641,496,208	25%
hornetmusic	111,546,446	12.5%
bobby.madagascar	1,870,146,486	5%
rishhk	68,889,275	15%
laissez-faire	56,390,112	100%
donefezy	1,804,722,149	5%
marfonso	17,425,604,174	5%
cryptoxicate	2,433,548,793	5%
jimbi	890,456,593	5%
mmunited	17,043,024,643	30%
minhaz007	1,793,478,412	5%
wav-dr	103,802,239	1%
bufinjer	3,127,944,945	50%
steemersayu907	9,406,301,079	5%
sammiegold	2,426,023,707	5%
raoufwilly	465,148,372	7.5%
nureza	359,788,267	5%
eonwarp	1,518,905,138	100%
solarphasing	136,191,637	2%
glastar	17,328,796,209	5%
theestateof.mind	23,597,194,256	5%
lagtvgames	19,618,374,651	5%
ketcom	29,246,918,010	5%
bitdreamcompany	2,579,762,794	5%
ferrate	2,469,975,398	5%
steemturbo	4,248,098,595	5%
mrsbozz	319,156,368	5%
cryptovues	35,363,139,865	5%
e-r-k-a-n	11,810,835,691	5%
circa	61,604,426,721	10%
snuff1	103,852,710	5%
eternalsuccess	724,426,157	1%
tankometry	975,991,399	5%
jetometry	969,855,614	5%
rockstarbm	869,771,517	5%
blockmonster	106,861,211	30%
trueexpressions	1,347,666,893	5%
tulwave	2,381,111,200	5%
brada2550	244,335,686	5%
paleotwist	1,139,699,702	5%
cryptokos	1,340,308,884	5%
hungrybear	2,297,848,002	5%
hans001	2,131,588,616	5%
c4cristi3	2,026,819,223	5%
foreversteem	360,638,343	5%
donchard2	2,027,373,377	5%
ano123	43,474,475,889	5%
gharrington44	22,641,083,228	5%
cryptofiloz	9,232,070,395	5%
q-news	325,953,720	5%
potence	175,036,070	5%
smalltall	1,381,411,119	20%
p1eter	196,538,818	5%
coreyssteemit77	16,471,907,509	5%
backscratcher	4,334,090,281	5%
x-tech	2,068,692,315	5%
gamersayu907	2,003,316,698	5%
red-devils	135,664,407	5%
itisfinished	1,741,312,482	5%
iakuapunka	259,914,551	100%
raspibot	321,036,500	100%
thecryptolink	2,008,949,681	5%
newtrailers	1,790,583,220	5%
trydice	5,378,585,378	5%
likwid	44,620,471,390	0.75%
dark.light	1,597,425,623	5%
haxxdump	5,869,225	5%
kajmiere	97,142,866	5%
evanstinger	1,907,385,047	5%
octalmage	2,910,239,676	5%
shneal	4,887,153,893	5%
myotherstuff	27,882,073,878	100%
cryptohustler101	930,594,630	5%
ph1102	996,908,946	5%
diceshark	371,291,413	11.25%
astil.codex	372,885,685	70%
map10k	4,676,783,807	1.29%
cryptodailyflash	1,793,254,136	5%
vxc	2,353,003,316	100%
makingblocks	10,666,027,413	12.5%
mindtrap-pal	48,222,966	10%
topcryptonews	19,473,525,505	5%
abh12345.pal	166,996,678	25%
abh12345.leo	398,401,559	80%
onealfa.leo	0	3.6%
onealfa.pal	53,076,540	10.67%
ph1102.pal	0	10%
lamico	0	5%
empoderat	1,711,333,969	5%
vxc.leo	146,494,771	25%
reversemagnetar	498,872,911	100%
ilias.fragment	343,048,999	70%
supremebape	36,537,813,664	5%
nomsinc	99,213,422	25%
kryptoking1	5,077,642,171	5%
cryptoroll	2,287,219,081	5%
pouchon4sports	2,471,118,361	5%
midlet-yourleo	80,459,107	5%
theblvcktable	1,859,267,930	5%
mapxv	13,083,972,477	1.37%
abh12345.stem	1,542,254,454	100%
discostick	1,996,129,086	5%
dropthatcode	1,794,429,970	5%
cryptoisa	23,836,842,019	5%
sasifuddin	350,398,999	100%
anggreklesta.alt	196,850,791	100%
steemer-sayu907	9,950,984,567	5%
james.photos	1,930,361,015	5%
thesnaps	1,984,984,122	5%
coolcaptures	1,910,945,255	5%
dappinvestor	2,076,447,119	5%
goinvesting	144,728,496	25%
stem.alfa	536,540,090	100%
freefalling	136,296,021	5%
dajigganaut	934,162,594	5%
ctl001	455,240,176	100%
vxc.stem	41,167,437	100%

`post_id`	79,609,288
`author`	tts
`permlink`	re-offsetfunctiontomakeexcelssumfunctiondynamic-pyfqqwwgre-20190831t142106
`category`	excel
`json_metadata`	{}
`created`	2019-08-31 14:21:06
`last_update`	2019-08-31 14:21:06
`depth`	1
`children`	0
`net_rshares`	0
`last_payout`	2019-09-07 14:21:06
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 SBD
`curator_payout_value`	0.000 SBD
`pending_payout_value`	0.000 SBD
`promoted`	0.000 SBD
`body_length`	379
`author_reputation`	-4,535,933,372,579
`root_title`	"OFFSET Function to make Excels SUM function Dynamic"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 SBD
`percent_steem_dollars`	10,000

`post_id`	79,638,375
`author`	paulag
`permlink`	px5re3
`category`	excel
`json_metadata`	{"tags":["excel"],"app":"steemit\/0.1"}
`created`	2019-09-01 15:08:30
`last_update`	2019-09-01 15:08:30
`depth`	2
`children`	0
`net_rshares`	0
`last_payout`	2019-09-08 15:08:30
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 SBD
`curator_payout_value`	0.000 SBD
`pending_payout_value`	0.000 SBD
`promoted`	0.000 SBD
`body_length`	65
`author_reputation`	224,445,607,823,384
`root_title`	"OFFSET Function to make Excels SUM function Dynamic"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 SBD
`percent_steem_dollars`	10,000

voter	rshares	pct
anomaly	162,483,327	1%
paulag	639,066,496,562	100%
steemcommunity	85,150,216,663	100%
theexcelclub	186,320,980,419	100%
myotherstuff	26,912,522,681	100%
abh12345.stem	1,517,226,865	100%