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 "CN". 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=" CN"),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: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=512636

Thanks. It works perfectly!!!

"daddylonglegs" wrote:


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: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=512636

Hi 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=" CN",EmployeeData!K:K,0),0))}

Regards
Phil

"CiaraG" wrote:

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 "CN". 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=" CN"),EmployeeData!K:K))}

Thanks,

Ciara