#VALUE! message for working formula

**Consty1** · 11-15-2012, 03:28 PM

I have a formula set up to tell me if a goal is scored in the 2nd half of a match. Cells C2:J2 contain blank cells for me to input goal times for the home team and cells M2:T2 for the away team.

So for example if the home side won the game 2-1 with goals scored in 25, 33, and 56 minutes then the formula would return a 1 in the target cell. If there was no goal in the 2nd half then a 0 would be returned. The 25 would be in cell C2, 33 in cell M2, and 56 in cell D2. The rest of the cells are left empty.

The working formula I have is =IF(OR(C2:J2>45,M2:T2>45),1,0) but this is returning #VALUE! in the target cell, if I click the function wizard the formula is working.

Basically I'm not sure why the formula is returning #VALUE! so if anyone knows why that would be great. I've attached a sample file.

Sample file.xlsx

**JieJenn** · 11-15-2012, 03:32 PM

I didn't read the problem, but I am guessing the formula should be

=IF(OR(SUM(C2:J2)>45,SUM(M2:T2)>45),1,0) ?

**djapigo** · 11-15-2012, 03:50 PM

Hi Consty,

What you need is an array enter (CTRL-SHIFT-ENTER) and not just ENTER... go in edit mode (F2) then use the array enter...

**daddylonglegs** · 11-15-2012, 03:51 PM

Your current formula works but it's an "array formula" and, as such, it needs to be "array entered". Select cell with formula, press F2 key to select formula and then hold down CTRL and SHIFT keys while pressing ENTER. If done correctly then curly braces like { and } will appear around the formula

....or you can try this non array version

=0+(SUMPRODUCT((C2:J2>45)+(M2:T2>45))>0)