Hi Vicente,

Try in E10 or

{ =SUM((LOOKUP((A2:A6);(A11:A13);(B11:B13))=D10)*((B 2:B6)))}

or

{=SUMPRODUCT(--(LOOKUP((A2:A6);(A11:A13);(B11:B13))=D10);(B2:B6)) }

Note that these are array formulas and have to be entered with
ctrl-shift-enter to put the {} around the function.

Note also that I'm using ";" instead of "," in functions. You have to change
this also if you are normally use "," in fucntions.

Hope this helps.

Wkr,

JP

"vsoler" wrote in message
...
My problem is the following:

In a range I have a list of expenses per department:

Dpt Expense
a 1
b 2
c 3
a 4
c 5

Say they are in A2:B6 (excluding headers)

As you can see, one department can have multiple expenses

In another range, I have a grouping of departments by area:

Dept Area
a m
b n
c m

Say they are in A11:B13 (excluding headers)

Now, given a certain Area in cell D10, (in the example m or n), what
formula will give the total expense for that Area?

Say that D10 contains 'm', the result should be 1+3+4+5 = 13.
Say that D10 contains 'n', the result should be 2.

Of course, my model is a lot bigger and more complex, but the basic
problem is explained above.

What I am looking for is a single formula, array or not, but I do not
want to use auxiliary cells for intermediate results.

Can this be done?