SOLVED

Excel365 formula spills for unknown reason

Occasional Visitor

I have a formula that I'm using to look for a Y in a range of cells and if it doesn't find that then looks for NA or N/A and if it does not find that the return a N

 

the formula

=IF(COUNTIF(H13:O13,"Y") >= 1,"Y",IF(COUNTIF(H13:O13,{"na","n/a"})>=1,"NA","N"))

 

psuedocode:

if(count of Y>=1){

  Value=Y

}else if(count of NA>=1

 Value=NA

}else{

 Value=N

}

in Excel 2016 if the formula is in P13 it works great

in excel365 both P13 and Q13 have data from the formula from spilling over. I do not understand why this formula spills. Please help me.

 

  

1 Reply
best response confirmed by jfetting (Occasional Visitor)
Solution

@jfetting 

In 365 second IF(COUNTIF(... returns an array from two elements for which there is not enough space, thus #SPILL error. Previous version by default returns first element of the array. You may try

=IF(COUNTIF(H13:O13,"Y") >= 1,"Y",IF(SUM(COUNTIF(H13:O13,{"na","n/a"}))>=1,"NA","N"))