Pulling off a specific value from a table

**daveus!** · 03-17-2010, 06:09 PM

Hi guys,

Thanks for the reply! However, I think I need slightly more than a Sumproduct formula.

Though what you have works, I need to be able to change the year as well so that the formula automatically works out what column to look at.

I've attached another example that should be clearer. I want to be able to change the values in cells A2,B2,C2 and D2 and the result to be displayed in G2.

Is this posssible?!

Thanks again for taking the time to help

**The Phil** · 03-17-2010, 06:34 PM

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.

**shg** · 03-17-2010, 06:44 PM

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.

**JeanRage** · 03-18-2010, 03:09 AM

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 ...

**The Phil** · 03-18-2010, 12:50 PM

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.

**shg** · 03-18-2010, 12:58 PM

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.

**The Phil** · 03-18-2010, 03:36 PM

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.