Help with editing formula

[phillyfan]

I have the following formula entered in my spreadsheet from last year and want to edit it.

{=(SUM(COUNTIF(AI4:AX4,$B$29:$AG$29))*20)+(SUM(COUNTIF(AZ4:BG4,$B$29:$AG$29))*40)+(SUM(COUNTIF(BI4:BL4,$B$29:$AG$29))*80)+(SUM(COUNTIF(BN4:BO4,$B$29:$AG$29))*160)+(SUM(COUNTIF(BQ4,$B$29:$AG$29))*320)+(AH4*10)}

It is from a sheet that tracks our office March Madness pool. The formula calculates maximum possible points after the 1st round. I want to change the weighting of the rounds this year with the Final Four games worth 120 pts (not 160 shown) and the Championship game worth 160 pts (not the 320 shown). $B$29:$AG$29 represent the teams that are still alive.

When I go into the formula and change those two values and hit enter it returns a number that is not correct. Also, the 2 brackets { and } are dropped from the beginning and the end. If I select the cell and place cursor at end and not even edit the line and hit Enter the same thing happens with the incorrect value.

I vaguely recall last year having to hit shift-enter or something like that but can't seem to get the formula to work.

Thanks for your help,

Curt

[dominicb]

Good evening Phillyfan

The formula you've entered is an array formula. The curly braces aren't typed in but added by Excel to denote an array is being used. I won't bore you with the details (have a look here if you're really interested http://www.cpearson.com/excel/array.htm)
All you need to know is that when you've changed the formula, don't just press enter - hold down ctrl + shift and then enter.

HTH

DominicB

[phillyfan]

Thanks for the help. That's what I needed, I figured it was something fairly easy. Just couldn't remember it.

[dominicb]

You're welcome. Thanks for the feedback. Always appreciated.

DominicB