I stumbled into this and just don't understand what is going on. I am not a sophisticated Excel person, BTW.
Assume that the cells A1:A10 contain various names (text). What exactly does the following function do.
COUNTIF($A$1:$A$10,$A$1:$A$10)
What i have observed is as follows.
If I put that formula above into cell B5 (or C5 or D5 ...), it seems to act the same as the formula Countif($A$1:$A$10,A5). And if I put COUNTIF($A$1:$A$10,$A$1:$A$10) into cell D9 the formula seems to act like Countif($A$1:$A$10,A9). And if I were to put COUNTIF($A$1:$A$10,$A$1:$A$10) into cell C14 (where 14 is outside the range of the input arrays) then the result seems to always be zero. All that I have read implies that 'Criteria' is a scalar of some kind, so maybe this is an accident of implementation.
Is COUNTIF architected to work with two array inputs? And if so how is the second array treated and is the result returned always a scalar value?
Thanks.
dave
ps. I ran into this when I discovered that the formula SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10)) will return the # of unique values at or above the row in which the formula is placed. There are all kinds of things that I don't understand here, starting with why use SUMPRODUCT, on something that (I thought) was a scalar value.
Bookmarks