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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

V
  • Question Question
SUM XLOOKUP
Replies
5
Views
253
StephenCrump
S
nburaq
  • Solved
Conditional Formatting last n rows as data entered
Replies
6
Views
347
Fluff
Fluff
M
  • Solved
SUM OF SALES REVENUE BASED ON SALESPERSON AND MONTH OF THE YEAR
Replies
4
Views
373
meppwc
M
vinzent
  • Question Question
Formula to get the 4th workday before end of month
Replies
8
Views
426
Tetra201
Tetra201
B
  • Question Question
Percent Change
Replies
4
Views
197
kvsrinivasamurthy
K

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

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