Formula for month and year-to-date data

J

jyroxxor

New Member
Joined
Feb 26, 2018
Messages
11
Excel 2013/2016
ABCDEFGH
Month
Sales
Sales
PurchaseSales
Purchase
SalesMonth
Sales

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] "]Trial Balance[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] "]Jan18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] "]Feb18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] "]Mar18[/TD]
[TD="align: right"][/TD]

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

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

[TD="align: center"]5[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]Month[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]Year-To-Date[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]$[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]$[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2300[/TD]
[TD="align: right"]3100[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]Month[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]Year-To-Date[/TD]

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

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3800[/TD]
[TD="align: right"]6900[/TD]

</tbody>
Sheet1​



Hi all,

Would like to check on formula for G7 & H7.

Cell F3 is drop down menu which for me to select data for particular month.

For example, if I select Month = Mar18.
Cell G7 (monthly data) would reflect sales for Mar18, which is $3800
Cell H7 (year-to-date data) would reflect total sales from Jan18 to Mar18, which is $6900
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
If every column has different row number, it'd be no so easy to write a formula to fit all scenarios.

Try this formula for monthly total:

=SUMIFS(INDIRECT(ADDRESS(4,MATCH(F3,A3:E3,0))&":"&ADDRESS(9,MATCH(F3,A3:E3,0))),A4:A9,"=Sales")
 
Upvote 0
Hi,

Try this : Ctrl+Shift+Enter NOT just Enter

G7 =SUMPRODUCT(SUMIF(A5:A9,"sales",INDEX(B5:D9,0,MATCH(G3,B3:D3,0))))
H7 =SUM(B5:INDEX(B5:D9,ROWS(A5:A9),SUM(--(MONTH(DATEVALUE(LEFT(G3,3)&" 1"))>=MONTH(DATEVALUE(LEFT(B3:D3,3)&" 1")))))*--(A5:A9="Sales"))



[TABLE="width: 630"]
<colgroup><col width="70" span="9" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[TD="class: xl63, width: 70"]D[/TD]
[TD="class: xl63, width: 70"]E[/TD]
[TD="class: xl63, width: 70"]F[/TD]
[TD="class: xl63, width: 70"]G[/TD]
[TD="class: xl63, width: 70"]H[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]Trial Balance[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Jan18[/TD]
[TD="class: xl63"]Feb18[/TD]
[TD="class: xl63"]Mar18[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Month[/TD]
[TD="class: xl63"]Mar18[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]$[/TD]
[TD="class: xl63"]$[/TD]
[TD="class: xl63"]$[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Sales[/TD]
[TD="class: xl63"]100[/TD]
[TD="class: xl63"]600[/TD]
[TD="class: xl63"]1100[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Month[/TD]
[TD="class: xl63"]Year-To-Date[/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]Sales[/TD]
[TD="class: xl63"]200[/TD]
[TD="class: xl63"]700[/TD]
[TD="class: xl63"]1200[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]$[/TD]
[TD="class: xl63"]$[/TD]
[/TR]
[TR]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]Purchase[/TD]
[TD="class: xl63"]300[/TD]
[TD="class: xl63"]800[/TD]
[TD="class: xl63"]1300[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Sales[/TD]
[TD="class: xl63"]3800[/TD]
[TD="class: xl63"]6900[/TD]
[/TR]
[TR]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]Purchase[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]900[/TD]
[TD="class: xl63"]1400[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]Sales[/TD]
[TD="class: xl63"]500[/TD]
[TD="class: xl63"]1000[/TD]
[TD="class: xl63"]1500[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
 
  • Like
Reactions: yky
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Month to date, last month to date, last year to date
Replies
2
Views
606
gazmoz17
G
R
  • Solved
Allocate between dates to months then summarise by month and year
Replies
7
Views
564
Eric W
Eric W
N
  • Locked
  • Question Question
Exccel - date formula that drives multiple other formulas
Replies
2
Views
390
Fluff
Fluff
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
541
mrshuck
M
LRATOZ
  • Question Question
In Excel 2021 what is the formula to return a date when the max value happened (Compare the same month over many years)
Replies
2
Views
1K
LRATOZ
LRATOZ

Forum statistics

Threads
1,223,275
Messages
6,171,122
Members
452,381
Latest member
Nova88

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