The "--" converts TRUE and FALSE to 1 and 0 respectively.

This expression will return an array of either TRUE or FALSE:

COUNTIF(B$3:B$13,A$3:A$14)=0

The "--" converts those either 1 or 0:

--(COUNTIF(B$3:B$13,A$3:A$14)=0)

Then the SUM function adds those 1s and 0s to get a total:

SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0))


--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:83b02c6892c7c@uwe...
Dear Biff,
Many thank for your explanation. Actually, another point of the Excel
function is that why you put "--" before (COUNTIF(B$3:B$13,A$3:A$14)=0)),
INDEX(.....!

What is the purpose of "--" if mix with Excel function!

Thank for your time and effort again!
Wilchong





T. Valko wrote:
Actually, may I ask you one question, what is the logic in your function
which can list the missing data from second set of data? I saw you have
put --
infront of COUNTIF function, what is that mean?

We use COUNTIF to identify which items are missing from column B.

INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$1 4)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3)))

The COUNTIF function returns an array of counts like this:

COUNTIF(B$3:B$13,A3) = 1
COUNTIF(B$3:B$13,A4) = 1
COUNTIF(B$3:B$13,A5) = 2
COUNTIF(B$3:B$13,A6) = 0
COUNTIF(B$3:B$13,A7) = 0
etc
etc

Where the result of that count is 0 we use this expression to calculate
what
row the corresponding value is located on.

ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1

That number is then passed to the INDEX function to produce the desired
result.

Dear Biff,
Thank you so much for your Excel demo file. I finally found out what
[quoted text clipped - 28 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1