Formula for calculating YTD values

[Suan]

Hi
I am trying to set up a table that, when you change the date every month for
a range of values by month it sums up the YTD value.
eg

Jan Feb Mar April May
June
Salaries 10000 12000 18000 18000 20000 20000
Annual Leave 6000 5000 5000 4500 5500 6000

for example , If we are in the month of April, if I enter the date April the
formula should calculate Jan - April Salaries and annual leave?
And if I change the date to June the formula should calculate value from Jan
to June.

Hope it makes sense

Thanks

 

[Max]

Assume the table you posted is in A1:G3
Assume the "month" will be input in A5, eg: Mar
You could place this in A6:
=SUMPRODUCT(--(OFFSET(B2,,,,MATCH($A$5,$B$1:$IV$1,0))))
to derive the salaries summation from Jan to Mar.
Copy A6 down to A7 to derive the similar summation for "Annual leave"
Reconfigure the above to suit your desired layout
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[T. Valko]

With your month name column headers in the range B1:M1

A10 = some month name

=SUM(B2:INDEX(B2:M2,MATCH(A10,B1:M1,0)))

 

[JoeU2004]

I am trying to set up a table that, when you change the date every month
for a range of values by month it sums up the YTD value.
[....]
If we are in the month of April, if I enter the date April the formula
should
calculate Jan - April Salaries and annual leave?
And if I change the date to June the formula should calculate value from
Jan to June.

Those sound like two very different problems to me. I wonder if you are
over-specifying your requirements in the latter, resulting in suggested
solutions that are more complicated than you need.

Do you really need to an arbitrary month through which to sum? For example,
if you have data through June, would you really ask for the sum through
April or any other month earlier than June?

Or do you really mean that you want the sum through the last month for which
you have data? And as you enter data for each later month, you want the sum
to automatically include the later months?

For the latter, if salaries are in B2:M2, the YTD total is simply
=SUM(B2:M2). Similarly, if annual leave amounts are in B3:M3, the YTD total
is =SUM(B3:M3).

Alternatively, if you might want a row ("Salaries YTD") that gives the YTD
amount for each month for which you have data. Starting in B4, for example,
and copying across through M4, enter:

=if(B2="","",SUM($B$2:B2))

As you copy that cell across, the formula becomes, in B3 for example:

=if(B3="","",SUM($B$2:B3))

Similar for "Annual leave YTD, use C2 and SUM($C$2:C2) to begin with.


----- original message -----