How to get YTD Summary
 

Hi,
I have a 12-m sales number. I know it is easy to get MTD number by using
vlookup, but is there a way to get YTD number when month changes?

For example, if I use a cell to input month, then use vlookup to get a
number. Then this number is used in vlookup to get MTD number. Could I use
the Month Input to get a YTD number. for example, if I put April , MTD shows
7, and YTd shows37?

A1 A2
A13
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sales 12 13 5 7 8 9 10 11 1 2 3 6

D1 D2
Jan 2
Feb 3
Mar 4 ...

How to get YTD Summary
 

Hi,

The following formula will do the trick, it assumes your titles are on row 1
with Jan in B1. The data directly below it. I put the month you want into
cell A5.

=SUM(OFFSET(A2,,1,,MATCH(A5,B1:M1,)))

--
Cheers,
Shane Devenshire
Microsoft Excel MVP



"zy1972" wrote:

Hi,
I have a 12-m sales number. I know it is easy to get MTD number by using
vlookup, but is there a way to get YTD number when month changes?

For example, if I use a cell to input month, then use vlookup to get a
number. Then this number is used in vlookup to get MTD number. Could I use
the Month Input to get a YTD number. for example, if I put April , MTD shows
7, and YTd shows37?

A1 A2
A13
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sales 12 13 5 7 8 9 10 11 1 2 3 6

D1 D2
Jan 2
Feb 3
Mar 4 ...

How to get YTD Summary
 

If Cell E9 is the Month and your sales figures are in B2: M2 then use this
formula:

=SUM(INDIRECT("B2:"&CHAR(E9+66)&"2"))


"zy1972" wrote:

Hi,
I have a 12-m sales number. I know it is easy to get MTD number by using
vlookup, but is there a way to get YTD number when month changes?

For example, if I use a cell to input month, then use vlookup to get a
number. Then this number is used in vlookup to get MTD number. Could I use
the Month Input to get a YTD number. for example, if I put April , MTD shows
7, and YTd shows37?

A1 A2
A13
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sales 12 13 5 7 8 9 10 11 1 2 3 6

D1 D2
Jan 2
Feb 3
Mar 4 ...

How to get YTD Summary
 

Thank you so much. I tried it and it works. Actually this question has
bothered me for a while. Now it is all solved. Thanks,

"ShaneDevenshire" wrote:

Hi,

The following formula will do the trick, it assumes your titles are on row 1
with Jan in B1. The data directly below it. I put the month you want into
cell A5.

=SUM(OFFSET(A2,,1,,MATCH(A5,B1:M1,)))

--
Cheers,
Shane Devenshire
Microsoft Excel MVP



"zy1972" wrote:

Hi,
I have a 12-m sales number. I know it is easy to get MTD number by using
vlookup, but is there a way to get YTD number when month changes?

For example, if I use a cell to input month, then use vlookup to get a
number. Then this number is used in vlookup to get MTD number. Could I use
the Month Input to get a YTD number. for example, if I put April , MTD shows
7, and YTd shows37?

A1 A2
A13
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sales 12 13 5 7 8 9 10 11 1 2 3 6

D1 D2
Jan 2
Feb 3
Mar 4 ...

How to get YTD Summary
 

Thanks, It also works. I like this comminity. people are so helpful.



"dhstein" wrote:

If Cell E9 is the Month and your sales figures are in B2: M2 then use this
formula:

=SUM(INDIRECT("B2:"&CHAR(E9+66)&"2"))


"zy1972" wrote:

Hi,
I have a 12-m sales number. I know it is easy to get MTD number by using
vlookup, but is there a way to get YTD number when month changes?

For example, if I use a cell to input month, then use vlookup to get a
number. Then this number is used in vlookup to get MTD number. Could I use
the Month Input to get a YTD number. for example, if I put April , MTD shows
7, and YTd shows37?

A1 A2
A13
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sales 12 13 5 7 8 9 10 11 1 2 3 6

D1 D2
Jan 2
Feb 3
Mar 4 ...