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

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:38 PM GMT

## Question

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.