Year to Date formula

exuberant · Dec 19, 2014

I am still trying to figure out which sums current month and all previous months.

For example, current month is June so the formula should sum up January to June. And this shall be dependent on the current month.

Thanks in advance

WarPigl3t · Dec 19, 2014

Explain more. You can't sum months. January plus february Plus March etc doesn't equal anything. It's not a number.

exuberant · Dec 19, 2014

WarPiglet said:
Explain more. You can't sum months. January plus february Plus March etc doesn't equal anything. It's not a number.

Sorry for the confusion,
I meant that A1 to L1 contains the months while A2 to L2 contains the values.
I need the year to date values based on the current month

Matt Allington · Dec 19, 2014

Well if Jan numbers are in cell A2 and then the values for every month after that are in cell B2, C2 etc. then put the following formula in cell A3 and copy across the page.

=sum($A$2:A2)

MARK858 · Dec 19, 2014

Removed as don't believe it is what the OP wants

FormR · Dec 19, 2014

exuberant said:
I meant that A1 to L1 contains the months while A2 to L2 contains the values.
I need the year to date values based on the current month

This assumes that your month values in A1 to L1 are text values and not dates formatted as mmm.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

59

2

8

3

5

1

10

1

5

9

4

2

7

59

Sheet1

pjmorris · Dec 19, 2014

maybe I'm missing something, but if it is only the current Year to date and you want the total in M2 why not simply sum the line: =SUM(A2:L2) This will work since future months will presumably have 0 value?

Just a thought.

exuberant · Dec 19, 2014

FormR said:
This assumes that your month values in A1 to L1 are text values and not dates formatted as mmm.

Sheet1

A B C D E F G H I J K L M N O P
1 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Dec 59 < Current Month manually input into cell N1
2 8 3 5 1 10 1 5 9 4 4 2 7 59 < Current Month automatically the month of today

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:36px;"><col style="width:29px;"><col style="width:45px;"><col style="width:45px;"><col style="width:331px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula
O1 =SUM(A2:INDEX(A2:L2,MATCH(N1,A1:L1,0)))
O2 =SUM(A2:INDEX(A2:L2,MATCH(TEXT(TODAY(),"mmm"),A1:L1,0)))

<tbody>
</tbody>

<tbody>
</tbody>

This is ace! You just saved my day! Thanks a lot!

FormR · Dec 19, 2014

exuberant said:
This is ace! You just saved my day! Thanks a lot!

Cool! Glad it worked.

pjmorris said:
future months will presumably have 0 value?

Not sure what the OP's situation is, but maybe the future values are forecasts or maybe even a prior year..

pjmorris · Dec 19, 2014

Hi FormR, I accept that either reason is highly likely, but sometimes the simplest solutions are sufficient.

Year to Date formula

exuberant

New Member

Excel Facts

WarPigl3t

Well-known Member

exuberant

New Member

Matt Allington

MrExcel MVP

MARK858

MrExcel MVP

FormR

MrExcel MVP

pjmorris

Well-known Member

exuberant

New Member

FormR

MrExcel MVP

pjmorris

Well-known Member

Similar threads

Forum statistics

Share this page

Year to Date formula

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

Well-known Member

New Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock