Sum of Data based on today's month

C

carjockey

New Member
Joined
Nov 28, 2018
Messages
4
Hello Everyone,

I have a row of sales data by month and I want to compare the sum of that row with the sum of another row of data from the same date range (ie: April-Sept 2019 total sales to April-Sept 2018 total sales), and then I want the formula to update the range of the sum when we record October sales data (the next month) so we can see April-October range. Thank You in Advance.
[TABLE="width: 544"]
<tbody>[TR]
[TD="align: right"]2019[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JUL[/TD]
[TD]AUG[/TD]
[TD]SEP[/TD]
[TD]OCT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2018[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JUL[/TD]
[TD]AUG[/TD]
[TD]SEP[/TD]
[TD]OCT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:37.07px;" /><col style="width:30.42px;" /><col style="width:34.22px;" /><col style="width:29.47px;" /><col style="width:27.56px;" /><col style="width:32.32px;" /><col style="width:30.42px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:31.37px;" /><col style="width:25.66px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">2018</td><td style="text-align:right; ">2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2019</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">APR</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">MAY</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">JUN</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">JUL</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">AUG</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">SEP</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">OCT</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">NOV</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">DEC</td><td > </td><td style="text-align:right; ">19</td><td style="text-align:right; ">31</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">8</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2018</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">APR</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">MAY</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">JUN</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">JUL</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">AUG</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">SEP</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">OCT</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">NOV</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">DEC</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >L2</td><td >=SUM(B8:INDEX($B$7:$J$8,2,MATCH(TEXT(TODAY(),"mmm"),$B$7:$J$7,0)))</td></tr><tr><td >M2</td><td >=SUM(B3:INDEX($B$2:$J$3,2,MATCH(TEXT(TODAY(),"mmm"),$B$2:$J$2,0)))</td></tr></table></td></tr></table>
 
Upvote 0
DanteAmor said:
Try this

ABCDEFGHIJKLM

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:37.07px;"><col style="width:30.42px;"><col style="width:34.22px;"><col style="width:29.47px;"><col style="width:27.56px;"><col style="width:32.32px;"><col style="width:30.42px;"><col style="width:33.27px;"><col style="width:33.27px;"><col style="width:31.37px;"><col style="width:25.66px;"><col style="width:76.04px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]APR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]MAY[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]JUN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]JUL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]AUG[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]SEP[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]OCT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]NOV[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]DEC[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]31[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]APR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]MAY[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]JUN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]JUL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]AUG[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]SEP[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]OCT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]NOV[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]DEC[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]3[/TD]

</tbody>

CellFormula
L2=SUM(B8:INDEX($B$7:$J$8,2,MATCH(TEXT(TODAY(),"mmm"),$B$7:$J$7,0)))
M2=SUM(B3:INDEX($B$2:$J$3,2,MATCH(TEXT(TODAY(),"mmm"),$B$2:$J$2,0)))

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...
Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Select current month in combobox on userform for English language
2 3
Replies
29
Views
334
Ali M
A
V
  • Question Question
SUM XLOOKUP
Replies
5
Views
266
StephenCrump
S
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
128
flashgordie
F
nburaq
  • Solved
Conditional Formatting last n rows as data entered
Replies
6
Views
362
Fluff
Fluff
M
  • Solved
SUM OF SALES REVENUE BASED ON SALESPERSON AND MONTH OF THE YEAR
Replies
4
Views
399
meppwc
M

Forum statistics

Threads
1,224,871
Messages
6,181,497
Members
453,047
Latest member
charlie_odd

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