Pulling off a specific value from a table

[daveus!]

Hi guys,

I was wondering if you could help me with the following.

I want a formula that will pull off a value from a table of data, based on specified critera.

I've attached an example data set to hopefully help explain.

Basically, I want to be able to put a formula in cell A1 that will say:

"look to see where YEAR = 2009, REGION = UK, PERSON = MAN1 and add up the values in the appropiate '£' column". I would then like it to return the value 6136.

I'm sure there must be an easy way to do this but I can't work it out...

I ideally don't want to use a pivot table or have to rearrange the data in anyway.

Many thanks for any help at all!

[JeanRage]

Hi dave,

See attached ...

HTH

[BigBas]

You can enter this formula in cell A1 to query to the 2009 column.

Let me know if this solution works.

Please Login or Register  to view this content.

[daveus!]

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]

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]

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]

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]

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]

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.

[daveus!]

Hi guys,

Thanks for the replies.

If think you're all right and I've therefore edited the way that my data is laid out. It isn't perfect, but at least the sum product formulas work.

Thanks for all the help!

[The Phil]

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.