Understanding formula's

If data and text are ranges of the same size then the formula is comparing the relative entries in each (using EXACT gives you a case-sensitive comparison) and for each row (or column) that matches 1 is returned by the IF function, so if there are 5 matches the formula will return 5 because it sums the 5 1s returned....

You could make it a non-array function by using SUMPRODUCT like this

=SUMPRODUCT(EXACT(data,text)+0)

The 1 is the TRUE part of the IF function. There is no FALSE part, though it could be 0.

data is a Named Range covering the cells to be tested. text is another Named Range with the text to compare to the data range.

It will generate a matrix of TRUE or FALSE and, for each TRUE, you will get a 1 added. For example, {TRUE, TRUE, FALSE, FALSE, TRUE} would give =SUM{1,1,0,0,1} = 3


Regards, TMS

Thanks for the rep