SOLVED

Excel365 formula spills for unknown reason

%3CLINGO-SUB%20id%3D%22lingo-sub-2271765%22%20slang%3D%22en-US%22%3EExcel365%20formula%20spills%20for%20unknown%20reason%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2271765%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20formula%20that%20I'm%20using%20to%20look%20for%20a%20Y%20in%20a%20range%20of%20cells%20and%20if%20it%20doesn't%20find%20that%20then%20looks%20for%20NA%20or%20N%2FA%20and%20if%20it%20does%20not%20find%20that%20the%20return%20a%20N%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20formula%3C%2FP%3E%3CP%3E%3DIF(COUNTIF(H13%3AO13%2C%22Y%22)%20%26gt%3B%3D%201%2C%22Y%22%2CIF(COUNTIF(H13%3AO13%2C%7B%22na%22%2C%22n%2Fa%22%7D)%26gt%3B%3D1%2C%22NA%22%2C%22N%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Epsuedocode%3A%3C%2FP%3E%3CP%3Eif(count%20of%20Y%26gt%3B%3D1)%7B%3C%2FP%3E%3CP%3E%26nbsp%3B%20Value%3DY%3C%2FP%3E%3CP%3E%7Delse%20if(count%20of%20NA%26gt%3B%3D1%3C%2FP%3E%3CP%3E%26nbsp%3BValue%3DNA%3C%2FP%3E%3CP%3E%7Delse%7B%3C%2FP%3E%3CP%3E%26nbsp%3BValue%3DN%3C%2FP%3E%3CP%3E%7D%3C%2FP%3E%3CP%3Ein%20Excel%202016%20if%20the%20formula%20is%20in%20P13%20it%20works%20great%3C%2FP%3E%3CP%3Ein%20excel365%20both%20P13%20and%20Q13%20have%20data%20from%20the%20formula%20from%20spilling%20over.%20I%20do%20not%20understand%20why%20this%20formula%20spills.%20Please%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2271765%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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"))