Apr 14 2021 12:24 AM
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.
Apr 14 2021 12:31 AM
SolutionIn 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"))
Apr 14 2021 12:31 AM
SolutionIn 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"))