Formula Refresh

**jcsmfg** · 01-17-2010, 11:42 AM

I have setup a new formula but does not seem to refresh automatically. A friend once told me to press "Alt&Enter" but does not seem to work. In his absence, can someone confirm how I need to do this? Thanks....

**royUK** · 01-17-2010, 11:46 AM

The F9 button will force calculation, but unless you have calculation set to manual the formula should recalculate

**shg** · 01-17-2010, 11:46 AM

Formulas in Excel normally refresh automatically when their dependents change. Try Tools > Options >Calculation, make sure Automatic is ticked.

**jcsmfg** · 01-17-2010, 12:00 PM

thanks both for such quick reply! i am still unable to refresh and do remember my friend talking about multiple pressed keys....any idea?

**jcsmfg** · 01-17-2010, 12:05 PM

please note the original formula is enclosed with "{" and "}", what does it mean?

**royUK** · 01-17-2010, 12:53 PM

Originally Posted by jcsmfg

please note the original formula is enclosed with "{" and "}", what does it mean?

That denotes an array formula & must be entered in a particular way - Press CTRL+SHIFT+ENTER not just ENTER, see

http://office.microsoft.com/en-us/ex...872901033.aspx

**jcsmfg** · 01-17-2010, 01:37 PM

it worked! any way to automate this? or simplify the equation?

=SUM(IF(FREQUENCY(IF('New'!$AC$2:$AC$157=A4,IF('New'!$Y$2:$Y$157=Tables!$L$2,IF('New'!$K$2:$K$157<>" ",MATCH("~"&'New'!$K$2:$K$157,'New'!$K$2:$K$157&"",0)))),ROW('New'!$K$2:$K$157)-ROW('New'!$K$2)+1),1))

AC is the first value I need to lookup
Y is a condition1 that must be met
K is to be counted but only if not repeated and condition Y is met