Cross referencing between columns and rows?

**mike ryan** · 12-29-2011, 09:39 AM

I'm not sure how best to explain this so have attached a spreadsheet that I hope will make things a bit clearer. Basically what I have in column A is a list of names, and along the top in row 1, are dates on a month by month basis from column B to F.

Underneath each month I have input a number.

Now things get tricky, you'll see that Ive entered dates in column's H&I. These dates represent the date the figure 1st appeared for that particular name, and the last date in the range (regardless of whether a number appears or not)

What I need to be able to do, is come up with a formula that "finds" the beginning date for each "name" and then initiates a sum from that date to the end of the range, eg =sum(b2:e2) for the 1st name.

Any ideas?

**greg doman** · 12-29-2011, 02:02 PM

I did this in two similar ways.

first use MATCH to find the position of the date in the range of headings
then use ROW() and ADDRESS to form the address of the cells
then use INDIRECT to pass the newly formed cell range to either SUBTOTAL OR SUMIF