software

Good morning all,

I am using the following array formuale in my worksheet to calculate the sum
of a column where one column is equal to a variable and another column is
equal to quot;CNquot;. My formulae keeps giving me the NUM error. This error
appears even when I use the SUMPRODUCT function. Has anyone any ideas on how
to amend this???

{=SUM(IF((EmployeeData!A:A=B8)*(EmployeeData!L:L=quot; CNquot;),EmployeeData!K:K))}

Thanks,

Ciara


I'd use SUMPRODUCT but either way you can't use complete column refs
like A:A - switch to A1:A1000 or similar--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=512636Hi Ciara.

Labels at the head of the columns might be the source of the problem.

Try the following syntax.

{=SUM(IF(EmployeeData!A:A=B8,If(EmployeeData!L:L=quot; CNquot;,EmployeeData!K:K,0),0))}

Regards
Phil

quot;CiaraGquot; wrote:

gt; Good morning all,
gt;
gt; I am using the following array formuale in my worksheet to calculate the sum
gt; of a column where one column is equal to a variable and another column is
gt; equal to quot;CNquot;. My formulae keeps giving me the NUM error. This error
gt; appears even when I use the SUMPRODUCT function. Has anyone any ideas on how
gt; to amend this???
gt;
gt; {=SUM(IF((EmployeeData!A:A=B8)*(EmployeeData!L:L=quot; CNquot;),EmployeeData!K:K))}
gt;
gt; Thanks,
gt;
gt; Ciara

Thanks. It works perfectly!!!

quot;daddylonglegsquot; wrote:

gt;
gt; I'd use SUMPRODUCT but either way you can't use complete column refs
gt; like A:A - switch to A1:A1000 or similar
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=512636
gt;
gt;