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"))



if(count of Y>=1){


}else if(count of NA>=1





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)


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"))