Formulas fail to calculate
 

Office 2007 -

I have quite a complex Excel workbook, and some of the simplest formulas
fail to calculate unless I set an array (F2, CTRL+SHIFT+ENTER).

This is a template for use by others and I do not want them to have to
perform this function. Why is this happening?

Example of a formula =sum(if(c1:h1="Type 1",c2:h2),0

Formulas fail to calculate
 

Change to sumproduct instead. Then NO array entering

=sum(if(c1:h1="Type 1",c2:h2),0

to sum c2:h2 if c1:h1=type 1
=sumproduct((c1:h1="Type 1")*c2:h2)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"YvonneW" wrote in message
...
Office 2007 -

I have quite a complex Excel workbook, and some of the simplest formulas
fail to calculate unless I set an array (F2, CTRL+SHIFT+ENTER).

This is a template for use by others and I do not want them to have to
perform this function. Why is this happening?

Example of a formula =sum(if(c1:h1="Type 1",c2:h2),0

Formulas fail to calculate
 

I think either :

1) You have recalculation set to manual

or

2) You need to format the cell with the formula as "General"



"YvonneW" wrote:

Office 2007 -

I have quite a complex Excel workbook, and some of the simplest formulas
fail to calculate unless I set an array (F2, CTRL+SHIFT+ENTER).

This is a template for use by others and I do not want them to have to
perform this function. Why is this happening?

Example of a formula =sum(if(c1:h1="Type 1",c2:h2),0

Formulas fail to calculate
 

Brilliant!!!! Thank you :-)

"Don Guillett" wrote:

Change to sumproduct instead. Then NO array entering

=sum(if(c1:h1="Type 1",c2:h2),0

to sum c2:h2 if c1:h1=type 1
=sumproduct((c1:h1="Type 1")*c2:h2)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"YvonneW" wrote in message
...
Office 2007 -

I have quite a complex Excel workbook, and some of the simplest formulas
fail to calculate unless I set an array (F2, CTRL+SHIFT+ENTER).

This is a template for use by others and I do not want them to have to
perform this function. Why is this happening?

Example of a formula =sum(if(c1:h1="Type 1",c2:h2),0