Pulling off a specific value from a table

It would seem that a sumproduct would work if the "C" was replaced by a formula that found the column that has the values of C2 and D2, in rows 5 and 6 respectively. It may need the ADDRESS function to do that, but I can't make it work. It would be something like:

=SUMPRODUCT(($A$7:$A$26=A2)*($B$7:$B$26=B2)*(address(7,[insert formula]):address(26:[insert formula])))

If someone can make a formula that figures out the column that matches cell C2 and D2 in his example, then inserting it into the above formula should work.

As shg points out, it is not because what you are asking for is feasible ...that it should be done ...
Revisitng the structure of your database makes a lot more sense ...and the pivot table will give you some much more flexibility ...

Originally Posted by shg

It would be better practice to have year as a separate column. The number of entries would double, and the columns would be reduced by two. Then the formulas are simple, or you could use a pivot table. That's the way Excel was designed to work.

Although it's not my spreadsheet, I just want to know what you mean. There are 2 columns for each year, one with amount and one with the No. What do you mean by creating a separate column for the year? What data would that column contain? Or do you mean another sheet below it? I'm just not sure what you are suggesting.

Originally Posted by shg

The year column would contain either 2009 or 2010, in the case of the data you have.

A database in Excel has one record per row and no blanks.

Ah, for some reason I was having a mental block and thinking that you were saying a column titled "2009" or "2010" and not "Year". One of those days I guess.