Bob, excellent start and so simple to absorb!
Quote Originally Posted by xld
=SUMPRODUCT(--(A2:A20="test"))

This negates every test, giving an array of -1s and 0s, and negates it again, giving an array of 1s and 0s. The single unary has the effect of coercing TRUE and FALSE to a number, the second switches the -1s to 1s, thereby not messing up our results.
Now i understand the use of the unary, can you tell me, as we are SUMming an array for an instance of text does that mean we have no use for the unary if SUMming numbers?, if we do use the unary (i assume we do really as you explained it changes the results from true/false to 0's & 1's) what is it's effect on numbers say if we were looking for -1 etc?