I have what I think is a simple formula, but I have an obvious error that I've apparently looked at too long and can't see. This returns a #SPILL! error.
= COUNTIF(I51, $A$1:$A$10)
What dumb error am I making?
CodePudding user response：
The correct syntax for COUNTIF is = COUNTIF(RANGE,CRITERIA), not = COUNTIF(CRITERIA, RANGE). If you reverse the two, which is what I think you did, you get an array of cells as the result, and depending on where you placed your formula, that may collide with other content, and so the array of cells you are returning cannot be displayed completely; hence the #SPILL! error.
If you use the wizard in Excel, it should guide you accordingly, especially if you are unsure regarding syntax.
CodePudding user response：
You will have greater success if you Reference the sheets by name e.g =COUNTIF('SHEET NAME'!$A$1:'SHEET NAME'!$A$10,**'SHEET NAME'!**I51).