Complex SUM

[vsoler]

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?

 

[JP Ronse]

Hi Vicente,

Try in E10 or

{ =SUM((LOOKUP((A2:A6);(A11:A13);(B11:B13))=D10)*((B2: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

 

[Ashish Mathur]

Hi,

Try this.

=SUMPRODUCT((LOOKUP($A$2:$A$6,$A$11:$A$13,$B$11:$B$13)=D10)*($B$2:$B$6))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[vsoler]

Hi Vicente,

Try in E10 or

{ =SUM((LOOKUP((A2:A6);(A11:A13);(B11:B13))=D10)*((B2: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

Thank you, JP Ronse, it works fine.

Now, since my model is really big, do you have an idea about which
solution is faster?

Thank you again.

 

[JP Ronse]

Hi Vicente,

I'm not sure but think that SUMPRODUCT will be fasted function.

By the way, you don't need to enter SUMPRODUCT as an array function.

Wkr,

JP

Hi Vicente,

Try in E10 or

{ =SUM((LOOKUP((A2:A6);(A11:A13);(B11:B13))=D10)*((B2: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

Thank you, JP Ronse, it works fine.

Now, since my model is really big, do you have an idea about which
solution is faster?

Thank you again.