Year-to-date based on current month

  • Thread starter Thread starter John in Toronto
  • Start date Start date
J

John in Toronto

Hey everyone,

I have an invoice schedule for the year. Each month I would like to
calculate the remaining unbilled amount for each client. My data is:
A B C D
1: Client January 1, 2008 February March....
2: ABC Co $30 $32 $25

So in January, the total amount unbilled for ABC Co is $57. In February, the
total amount unbilled for ABC Co is $25.

I've searched the forum, and the best answer came from Roger Govier in 2007
("YTD sum range"). Modifying his formula, I get:
=SUM(B2:INDEX(B2:M2,MATCH(Month(TODAY()), Month(B1:M2,0))))

The result is #Value with the Month function added, and #N/A without it.

Not sure what I'm doing wrong.

Thanks,

John
 
You need to replace the text months with real dates, then you can just use a
custom format to
display it like you want, or use this

=SUM(B2:INDEX(B2:M2,MATCH(TEXT(TODAY(),"mmmm"),B1:M1,0)))


--


Regards,


Peo Sjoblom
 
Peo,

That almost solved it...I had to change the months from dates to text
("January", "February",...).

It works now.

Thank you very much.

John
 
Back
Top