Calculate YTD total by month name

T

taashi

New Member
Joined
Mar 27, 2009
Messages
19
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
Hi, this is my sample sheet, I need YTD total in each column by product once the month name changes. below is my data set, please can you advise which formula will work for YTD Net sales calculation and also for Quarter to date Net Sales figures.


1624286534099.png
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
SUMIFS()
should work , although if you change the title ACT-2020 so it matches the data ACT 2020
Not sure of columns or rows in your picture
If you use 2XLBB - see add-in in menu or my signature
OR use something like onedrive / dropbox and link to a sample spreadsheet

Not sure what you mean by
I need YTD total in each column by product once the month name changes.
Click to expand...
 
Upvote 0
sample.xlsx
ABCD
1Mar
2Net Sales
3YTDAct 2021Bud 2021Act 2020
4Product A
5product B
6Product C
7Product D
8
9
10
11
12ProductYearMonthNet Sales
13Product AAct 2020Jan100
14product BAct 2020Jan150
15Product CAct 2020Jan120
16Product DAct 2020Jan180
17Product AAct 2020Feb80
18product BAct 2020Feb50
19Product CAct 2020Feb70
20Product DAct 2020Feb90
21Product AAct 2020Mar78
22product BAct 2020Mar86
23Product AAct 2021Jan90
24product BAct 2021Jan78
25Product CAct 2021Jan86
26Product DAct 2021Jan120
27Product AAct 2021Feb110
28product BAct 2021Feb100
29Product CAct 2021Feb150
30Product DAct 2021Feb120
31Product AAct 2021Mar180
32product BAct 2021Mar80
33Product CAct 2021Mar50
34Product DAct 2021Mar70
35Product AAct 2021Apr90
36product BAct 2021Apr78
37Product CAct 2020Mar120
38Product DAct 2020Mar110
39Product AAct 2020Apr100
40product BAct 2020Apr150
41Product CAct 2020Apr120
42Product DAct 2020Apr180
43Product AAct 2020May80
44product BAct 2020May50
45Product CAct 2020May70
46Product DAct 2020May90
47Product AAct 2020Jun78
48product BAct 2020Jun86
49Product CAct 2020Jun120
50Product DAct 2020Jun110
51Product CAct 2021Apr86
52Product DAct 2021Apr120
53Product AAct 2021May110
54product BAct 2021May90
55Product CAct 2021May78
56Product DAct 2021May86
57Product AAct 2021Jun120
58product BAct 2021Jun110
59Product CAct 2021Jun100
60Product DAct 2021Jun150
61Product ABud 2021Jan110
62product BBud 2021Jan100
63Product CBud 2021Jan150
64Product DBud 2021Jan120
65Product ABud 2021Feb180
66product BBud 2021Feb80
67Product CBud 2021Feb50
68Product DBud 2021Feb70
69Product ABud 2021Mar90
70product BBud 2021Mar78
71Product CBud 2021Mar86
72Product DBud 2021Mar120
73Product ABud 2021Apr110
74product BBud 2021Apr90
75Product CBud 2021Apr78
76Product DBud 2021Apr86
77Product ABud 2021May120
78product BBud 2021May110
79Product CBud 2021May100
80Product DBud 2021May150
81Product ABud 2021Jun110
82product BBud 2021Jun100
83Product CBud 2021Jun150
84Product DBud 2021Jun120
Sheet1
Cells with Data Validation
CellAllowCriteria
A1ListJan,Feb,Mar,Apr,May,Jun
 
Upvote 0
here is the mini sheet, thank you for your advise but sumif did not work as i need YTD total for specific month and it will need to change with the change in the name of month.

sample.xlsx
ABCD
1Mar
2Net Sales
3YTDAct 2021Bud 2021Act 2020
4Product A
5product B
6Product C
7Product D
8
9
10
11
12ProductYearMonthNet Sales
13Product AAct 2020Jan100
14product BAct 2020Jan150
15Product CAct 2020Jan120
16Product DAct 2020Jan180
17Product AAct 2020Feb80
18product BAct 2020Feb50
19Product CAct 2020Feb70
20Product DAct 2020Feb90
21Product AAct 2020Mar78
22product BAct 2020Mar86
23Product AAct 2021Jan90
24product BAct 2021Jan78
25Product CAct 2021Jan86
26Product DAct 2021Jan120
27Product AAct 2021Feb110
28product BAct 2021Feb100
29Product CAct 2021Feb150
30Product DAct 2021Feb120
31Product AAct 2021Mar180
32product BAct 2021Mar80
33Product CAct 2021Mar50
34Product DAct 2021Mar70
35Product AAct 2021Apr90
36product BAct 2021Apr78
37Product CAct 2020Mar120
38Product DAct 2020Mar110
39Product AAct 2020Apr100
40product BAct 2020Apr150
41Product CAct 2020Apr120
42Product DAct 2020Apr180
43Product AAct 2020May80
44product BAct 2020May50
45Product CAct 2020May70
46Product DAct 2020May90
47Product AAct 2020Jun78
48product BAct 2020Jun86
49Product CAct 2020Jun120
50Product DAct 2020Jun110
51Product CAct 2021Apr86
52Product DAct 2021Apr120
53Product AAct 2021May110
54product BAct 2021May90
55Product CAct 2021May78
56Product DAct 2021May86
57Product AAct 2021Jun120
58product BAct 2021Jun110
59Product CAct 2021Jun100
60Product DAct 2021Jun150
61Product ABud 2021Jan110
62product BBud 2021Jan100
63Product CBud 2021Jan150
64Product DBud 2021Jan120
65Product ABud 2021Feb180
66product BBud 2021Feb80
67Product CBud 2021Feb50
68Product DBud 2021Feb70
69Product ABud 2021Mar90
70product BBud 2021Mar78
71Product CBud 2021Mar86
72Product DBud 2021Mar120
73Product ABud 2021Apr110
74product BBud 2021Apr90
75Product CBud 2021Apr78
76Product DBud 2021Apr86
77Product ABud 2021May120
78product BBud 2021May110
79Product CBud 2021May100
80Product DBud 2021May150
81Product ABud 2021Jun110
82product BBud 2021Jun100
83Product CBud 2021Jun150
84Product DBud 2021Jun120
Sheet1
Cells with Data Validation
CellAllowCriteria
A1ListJan,Feb,Mar,Apr,May,Jun
 
Upvote 0
not sure what you mean exactly by
as i need YTD total for specific month
Click to expand...
Can you give some examples of results and Why

guessing
you are showing Mar in column A1
does this mean you want to sum JAN , FEB & MAR

to do this I have added an extra column (E) and a cell (B1) that converts the TEXT JAN , FEB etc into a Number 1 to 12 for the month
then used a SUMIFS()

=SUMIFS($D$13:$D$84,$A$13:$A$84,$A4,$B$13:$B$84,B$3,$E$13:$E$84,"<="&$B$1)

Note by adding the month helper column as a number and using the selection in A1 to produce a number in B1 - we can use Less than or = to get a YTD upto and including the month in A1

Anywhere near what you want


Book2
ABCDE
1Mar3
2Net Sales
3YTDAct 2021Bud 2021Act 2020
4Product A380380258
5product B258258286
6Product C286286310
7Product D310310380
8
9
10
11
12ProductYearMonthNet SalesMonth No.
13Product AAct 2020Jan1001
14product BAct 2020Jan1501
15Product CAct 2020Jan1201
16Product DAct 2020Jan1801
17Product AAct 2020Feb802
18product BAct 2020Feb502
19Product CAct 2020Feb702
20Product DAct 2020Feb902
21Product AAct 2020Mar783
22product BAct 2020Mar863
23Product AAct 2021Jan901
24product BAct 2021Jan781
25Product CAct 2021Jan861
26Product DAct 2021Jan1201
27Product AAct 2021Feb1102
28product BAct 2021Feb1002
29Product CAct 2021Feb1502
30Product DAct 2021Feb1202
31Product AAct 2021Mar1803
32product BAct 2021Mar803
33Product CAct 2021Mar503
34Product DAct 2021Mar703
35Product AAct 2021Apr904
36product BAct 2021Apr784
37Product CAct 2020Mar1203
38Product DAct 2020Mar1103
39Product AAct 2020Apr1004
40product BAct 2020Apr1504
41Product CAct 2020Apr1204
42Product DAct 2020Apr1804
43Product AAct 2020May805
44product BAct 2020May505
45Product CAct 2020May705
46Product DAct 2020May905
47Product AAct 2020Jun786
48product BAct 2020Jun866
49Product CAct 2020Jun1206
50Product DAct 2020Jun1106
51Product CAct 2021Apr864
52Product DAct 2021Apr1204
53Product AAct 2021May1105
54product BAct 2021May905
55Product CAct 2021May785
56Product DAct 2021May865
57Product AAct 2021Jun1206
58product BAct 2021Jun1106
59Product CAct 2021Jun1006
60Product DAct 2021Jun1506
61Product ABud 2021Jan1101
62product BBud 2021Jan1001
63Product CBud 2021Jan1501
64Product DBud 2021Jan1201
65Product ABud 2021Feb1802
66product BBud 2021Feb802
67Product CBud 2021Feb502
68Product DBud 2021Feb702
69Product ABud 2021Mar903
70product BBud 2021Mar783
71Product CBud 2021Mar863
72Product DBud 2021Mar1203
73Product ABud 2021Apr1104
74product BBud 2021Apr904
75Product CBud 2021Apr784
76Product DBud 2021Apr864
77Product ABud 2021May1205
78product BBud 2021May1105
79Product CBud 2021May1005
80Product DBud 2021May1505
81Product ABud 2021Jun1106
82product BBud 2021Jun1006
83Product CBud 2021Jun1506
84Product DBud 2021Jun1206
Sheet1
Cell Formulas
RangeFormula
B1B1=MONTH(DATEVALUE(A1&"1"))
B4:D7B4=SUMIFS($D$13:$D$84,$A$13:$A$84,$A4,$B$13:$B$84,B$3,$E$13:$E$84,"<="&$B$1)
E13:E84E13=MONTH(DATEVALUE(C13&"1"))
 
Upvote 0
Hi etaf,
Many thanks for helping me out.
I have added the month number and the formula is working now and everything is calculated just fab.
thanks for the brilliant advise.
best.
 
Upvote 0
you are welcome,

I have in the past used a method to do this without a helper column , but cant remember or find the example , it would have been in reply to either this forum or one other forum.

But I'm sure more experienced members may have a solution without helper column, although not sure how the performance with arrays would be impacted
 
Upvote 0
You can try SumProduct.
N.B. The range C13:C84 has actual dates like 01-01-2020; these dates are formatted to show month like "Jan".

T202106a.xlsm
ABCD
1Mar3
2Net Sales
3YTDAct 2021Bud 2021Act 2020
4Product A380380258
5product B258258286
6Product C286286310
7Product D310310380
8
9
10
11
12ProductYearMonth_Net Sales
13Product AAct 2020Jan100
14product BAct 2020Jan150
15Product CAct 2020Jan120
16Product DAct 2020Jan180
17Product AAct 2020Feb80
18product BAct 2020Feb50
19Product CAct 2020Feb70
20Product DAct 2020Feb90
21Product AAct 2020Mar78
22product BAct 2020Mar86
23Product AAct 2021Jan90
24product BAct 2021Jan78
25Product CAct 2021Jan86
26Product DAct 2021Jan120
27Product AAct 2021Feb110
28product BAct 2021Feb100
29Product CAct 2021Feb150
30Product DAct 2021Feb120
31Product AAct 2021Mar180
32product BAct 2021Mar80
33Product CAct 2021Mar50
34Product DAct 2021Mar70
35Product AAct 2021Apr90
36product BAct 2021Apr78
37Product CAct 2020Mar120
38Product DAct 2020Mar110
39Product AAct 2020Apr100
40product BAct 2020Apr150
41Product CAct 2020Apr120
42Product DAct 2020Apr180
43Product AAct 2020May80
44product BAct 2020May50
45Product CAct 2020May70
46Product DAct 2020May90
47Product AAct 2020Jun78
48product BAct 2020Jun86
49Product CAct 2020Jun120
50Product DAct 2020Jun110
51Product CAct 2021Apr86
52Product DAct 2021Apr120
53Product AAct 2021May110
54product BAct 2021May90
55Product CAct 2021May78
56Product DAct 2021May86
57Product AAct 2021Jun120
58product BAct 2021Jun110
59Product CAct 2021Jun100
60Product DAct 2021Jun150
61Product ABud 2021Jan110
62product BBud 2021Jan100
63Product CBud 2021Jan150
64Product DBud 2021Jan120
65Product ABud 2021Feb180
66product BBud 2021Feb80
67Product CBud 2021Feb50
68Product DBud 2021Feb70
69Product ABud 2021Mar90
70product BBud 2021Mar78
71Product CBud 2021Mar86
72Product DBud 2021Mar120
73Product ABud 2021Apr110
74product BBud 2021Apr90
75Product CBud 2021Apr78
76Product DBud 2021Apr86
77Product ABud 2021May120
78product BBud 2021May110
79Product CBud 2021May100
80Product DBud 2021May150
81Product ABud 2021Jun110
82product BBud 2021Jun100
83Product CBud 2021Jun150
84Product DBud 2021Jun120
85
4cc
Cell Formulas
RangeFormula
B1B1=MONTH(1&A1)
B4:D7B4=SUMPRODUCT(Net_Sales,--(Product=$A4),--(Year=B$3),--(MONTH(Month_)<=$B$1))
Named Ranges
NameRefers ToCells
'4cc'!Month_='4cc'!$C$13:$C$84B4:D7
'4cc'!Net_Sales='4cc'!$D$13:$D$84B4:D7
'4cc'!Product='4cc'!$A$13:$A$84B4:D7
'4cc'!Year='4cc'!$B$13:$B$84B4:D7
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
Sumproduct sumif indirect
Replies
5
Views
366
awoohaw
awoohaw
S
  • Question
YTD Calculations using SumProducts & Offset
2
Replies
10
Views
1K
awoohaw
awoohaw
S
  • Question
Help in creating generic month + previous month formula to calculate YTD
Replies
4
Views
464
Bosquedeguate
Bosquedeguate
M
  • Question
YTD Calculation using dynamic columns and conditions
Replies
2
Views
862
mrxell
M
J
  • Solved
How to calculate YTD Actuals and ignore future month's forecast
Replies
3
Views
640
awoohaw
awoohaw

Forum statistics

Threads
1,214,832
Messages
6,121,841
Members
449,051
Latest member
excelquestion515

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