Complex SUM
 

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?

Complex SUM
 

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?

Complex SUM
 

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" 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?

Complex SUM
 

On Sep 6, 1:14*pm, "JP Ronse" wrote:
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?

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.

Complex SUM
 

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


"vsoler" wrote in message
...
On Sep 6, 1:14 pm, "JP Ronse" wrote:
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?

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.