John,

In the case of SUMPRODUCT, the arguments are arrays. So even though COUNTIF only looks at one condition at a time, SUMPRODUCT will go through each cell in the array. You could think about it as though the formula was written as an array formula this way:

{=SUM(1/COUNTIF(A1:A4, A1:A4))}

Excel would look at the array above and translate it as:

1/COUNTIF(A1:A4, A1) +
1/COUNTIF(A1:A4, A2) +
1/COUNTIF(A1:A4, A3) +
1/COUNTIF(A1:A4, A4)

It might be easiest to see how the function works by using the "Evaluate Formula" button on the Formulas tab to step through the calculations one at a time.

Ben