Formula to lookup the title name to decide what column to sum to

QuestionEdit

QUESTION: I have a regular 12 month's of data financial statement. The statement has departments in the rows, and each month of revenue and expenses in the columns. The heading for each of the columns is "Jan," "Feb," and so on. At the top of theworksheet is a Title which is adjusted each month as the latest month of financial data populates the spreadsheet. For example "Budget to actual" and "Year-to-date January - September." I want to create a formula in the Total column, where I can simply change the month in the Title, and all of the fomula's in the "Total Column" will know to sum to the month specified in the Title. I was thinking it should be something like Sum(B10:Lookup(Mid,A2,25,3),B5:M5,N10). Where A2 is the Title of the report, B5 through M5 are the column headings of Jan, Feb, Mar, and N10 is in the Total's Colum. ANSWER: You'd use a combination of an INDEX and a MATCH function:

A1:H11 contains your entire table of data, row 1 (A1:H1) has the month names, cell I4 contains the month to sum.

=SUM(INDEX($A$1:$H$11,,MATCH(I4,$A$1:$H$1,0)))

---------- FOLLOW-UP ----------

QUESTION: I like the idea of using the index and match, but I think this formula is not taking into account a few things: 1. I need the sum to be able to total only the items in its same row for the previous months of the year, and; 2. I need the formula to be able to look at whatever ending month I change the title to (ex. January - August, January - September). I need the formula to know I need it to sum up only the items from January through August in the first example, and January through September in the second. I am thinking that I can do an index/match where I tell the formula to match the first three letters of the ending month to the title (ex. august, september) to the column with the data (ex.aug, sep) . I attached the image.

AnswerEdit

OK, you didn't specifically state that you needed a YTD sum on the same row. I would have a separate set of two cells, which hold the starting month and the ending month and use those two cells both for the title and for the YTD functions. I would then have two additional cells that contain the index number of the month.

Say your start and end months are in cells N1 and N2. Next to those two, in O1 and O2, have this formula to extract the index: =MATCH(Z1,$A$1:$H$1,0)

Then on e.g. row 3 of the table: =SUM(INDEX($A3:$L3,1,$O$1):INDEX($A3:$L3,1,$O$2))