Return value of cell based on 2 other cells within a table

**hugzy** · 08-19-2011, 01:51 PM

Hi all - would love some help on this...

I have a tabular data set of 12 or so columns. 3 of these columns are of particular interest (country, project, cost.) 1 countries have multiple projects; the same project is listed against multiple countries.

I have created a grid within a separate excel worksheet with country down one side and project along the top. Within each cell in this grid, I want to return the value of the cost of that project within that country from my data set. How can I do this?

Solutions much appreciated!

Thx

**NBVC** · 08-19-2011, 01:57 PM

You can use SUMIFS...

e.g.

=SUMIFS('Sheet1'!C:C,'Sheet1'!A:A,$A2,'Sheet1'!B:B,B$1)

where it is assumed that the columns of interest are in Sheet1, column A:C and the countries in column A, Project in column B, costs in column C.

And in the active sheet the countries are listed starting in A2 and the projects listed starting in B1, the formula would then be in B2, copied down and across.