Hello,
I'm curious about something, and was wondering if anyone could shed some
light on it for me. I have a list that I wanted to count unique values from.
The list is generated using a formula that contains the OFFSET function.
The list is text with no blanks. The formula that I used to count the unique
values is

=SUM(IF(FREQUENCY(MATCH(C5:C14,C5:C14,0),MATCH(C5: C14,C5:C14,0)),1,0))

(I used it both as and not as an array formula)

It has always worked in the past for me, but this time returned #N/A. After
much putzing around and playing with it, I found that the above formula
always returns that error when the list you are counting contains formulas
with the OFFSET function in them. That confused me, because I couldn't
figure out why that would be the case. When I use the formula auditing
toolbar and showed the calculation steps, the last step is:

SUM({1;1;1;0;0;1;0;1;0;0;0})

which evaluates to #N/A.

I was able to get the formula working in my spreadsheet by replacing the
OFFSETs in my list generating formulas with a combination of VLOOKUP and
HLOOKUP, but I'm still perplexed by this not working based on the function
used to retrieve the values in the list. If anyone has heard of this or has
any ideas why it might work that way, I'd love to know.

Thanks to all.

Excel 2003, WinXPPro SP2
--
Mike Lee
McKinney,TX USA