Formulas fail to calculate

[YvonneW]

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

 

[Don Guillett]

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)

 

[dhstein]

I think either :

1) You have recalculation set to manual

or

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

 

[YvonneW]

Brilliant!!!! Thank you

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
(e-mail address removed)