Help: Formula for automatic YTD adjustment for an income statement

[198four]

I am trying to design a spreadsheet for an a company's income statement that will show income by month from Jan-Dec.
There will be a "Year to date (YTD)" column. Can someone tell me the formula I can use to give me a total upto current month in that column.

So for example:

If we need a YTD sum of revenues from January to August ....it will give me that....but in september , i don't wanna have to change the formulas for each cell and instead, if lets say I just plug in september as a month, it will automatically adjust to number of columns it needs to add up?

Makes sense?

It might be the IF function but how exactly i need to know.

Thank in advance

[Tony Valko]

We would need to see how your data is structured.

Can you post a SMALL sample file and show us what result you expect?

[198four]

Thanks for the quick reply! here's a sample file. So for example, YTD currently sums Jan to August but I would like the functionality where I can just punch in a date in one cell ..in cell A1 for example i punch in september, and it would automatically adjust the formula to sum Jan to Sep columns.

[DBY]

Hello
One way using Sum, Index and Match:

Please Login or Register  to view this content.

In cell S12. Cell A1 would contain the month as in the table headers: 'Jan'; 'Feb' etc. If you wanted to punch-in actual dates in A1 the formula would need amending.

Hope this helps.
DBY

[Tony Valko]

Try this...

=SUM(F12:INDEX(F12:Q12,MATCH(A1,F10:Q10,0)))

[198four]

Works! but can you explain that formula? Is there an easier way to do this? Maybe SUMIF function?

[198four]

See the attached file...im trying to use SUMIF function..what am i doing wrong?

sample2.xls

[DBY]

Hello
A1 is text. Sumif would need a numeric value for its <= criteria.

DBY

[198four]

Can you then assign numbers to those columns? And then use that as a ref. for example Jan =1 , Dec =12?

[198four]

So this formula works

=SUMIF(F9:Q9, "<="&A1, F12:Q12)

I used F9:Q9 to list number 1-12 for Jan to Dec

[DBY]

You would still have to create a numerical reference for A1, for example:

Please Login or Register  to view this content.

[198four]

Can you explain what purpose is index function serving in your formula? Are both these formulas equally as useful?

[198four]

Also, this formula

=SUMIF(F9:Q9, "<="&A1, F12:Q12)


Is working fine, so then what is the purpose of including "match" function in there?

Sorry for these questions, I am kind of a n00b to excel functions.

[DBY]

Sorry the formula:

Please Login or Register  to view this content.

Doesn't work for me. It returns zero.

The Index formula returns an array of values based on the array and Column/Row specified.

[198four]

Sorry the formula:

Please Login or Register  to view this content.

Doesn't work for me. It returns zero.

The Index formula returns an array of values based on the array and Column/Row specified.

In A1...use a number that pertains to the month instead of the month e.g. 6 for june, 12 for december

[DBY]

Yes, then it will work. As I said the A1 reference needs to be numeric for the <= criteria.

[198four]

ok so one more question,...what if we want a similar formula for "Quarter to Date (QTD)"? So lets say we are in august and I want to add numbers for Quarter to date..which would be July and August...how can we do so by using both those formulas?

[DBY]

Might need a little time to think this through. For example is Q1 Jan-Mar etc. and is A1 a numerical value?

[198four]

One thing we can do is add bunch of sumif formulas so something like

=SUMIF(F9:Q9, "="&A1, F12:Q12)+SUMIF(F9:Q9, "="&A2, F12:Q12)

So for Q3 uptil august, we can have 7 in A1 and 8 in A2

A better way of doing it?

[DBY]

Are you using Excel 2010? If so, we can use SUMIFS.

[DBY]

If you are using 2010, then maybe using a lookup table for the Quarters and a number of named ranges for ease of reference. See attachment.

DBY

[Tony Valko]

Try this...

Enter the quarter numbers in G10:R10...


Data Range

	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
10	QTR	1	1	1	2	2	2	3	3	3	4	4	4
11	2011	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		YTD	QTD
12	Revenue	10	20	30	40	50	60	70	80	90	100	110	120		450	240
13	Exp	2	3	4	5	6	7	8	9	10	11	12	13
14	Op Inc.	8	17	26	35	44	53	62	71	80	89	98	107

Then, this formula in U12:

=SUMIF(G10:R10,CEILING(MONTH(NOW())/3,1),G12:R12)

[DBY]

Just to add another alternative to Tony's and my earlier example (which perhaps is a bit over engineered). If you were to use the Index function, then maybe as in this attachment, it doesn't require helper rows or lookup tables on the sheet. Once again it assumes you are inputting the month as a number rather than text.

DBY