C-S-E Function Help

Posted by Giles on February 12, 2002 1:34 AM

Please can someone tell me if this is possible:-

I am starting to use the CSE function as explained in a past tip of the week and it is working well but I want to know if it can be expanded.

eg.
Column A : <Location List> North, South, East, West South.etc
Column B : <Salesman> John, Jim
Column C : <Sales figure > 10, 20, 50 etc

At the moment if i want to get the sum of figures for South Region i am using:
=sum(if(a1:a10=A30,c1:c10)) &LT;a30 being"South">

Is there a way to get a value for the Southern Region with the name Jim at the side? I have tried the following but it comes up with the wrong answer:

=sum(if(a1:a10=a30,and(b1:b10=b30),c1:10))
&LT;where b30="Jim">

Thanks for any help...

Posted by Aladin Akyurek on February 12, 2002 3:19 AM

Giles --

> At the moment if i want to get the sum of figures for South Region i am using:
=sum(if(a1:a10=A30,c1:c10))

When A30 = South, you have a single criterion/condition. Although the above formula array-entered is correct to get the intended sum, it's better to use SUMIF in cases of summing with a single condition:

=SUMIF(A1:A10,A30,C1:C10)

> Is there a way to get a value for the Southern Region with the name Jim at the side? I have tried the following but it comes up with the wrong answer:

> =sum(if(a1:a10=a30,and(b1:b10=b30),c1:10))
> &LT;where b30="Jim">

Yes. This is precisely a case of multiconditional sum that requires an array (CSE) formula:

{=sum(if((A1:A10=A30)*(B1:B10=B30),C1:C10))}

or, written without IF,

{=SUM((A1:A10=A30)*(B1:B10=B30)*C1:C10))}

Another way to get a multiconditional sum is with SUMPRODUCT. What follows does not require CSE:

=SUMPRODUCT((A1:A10=A30)*(B1:B10=B30)*C1:C10))

Aladin

=============