On 2002-03-22 07:35, Ian Mac wrote:
Aladin, I was a bit hasty with my question and didn't fully explain the problem, the OFFSET(.........) part of my orignal question is the dynamic range.
I agree with the NON use of Address, what I should of put is 'my named range is AddressOfCustomer' (me being hasty)
I have since looked at it at realised that I had forgotten to include -1 after the COUNTA():
=OFFSET(Listing!$I$2,0,0,COUNTA(Listing!$I:$I)-1,1)
Because I needed the range to start in row 2 as the first row is field headings (and of they're going to be unique ), and it was taking the range 1 row beyond my needs, Perhaps now I should mention that the error was a #DIV/0! (you doubtless would have figured it out had I told you (hasty hasty hasty)).
Right. Hope you don't have in-between blanks in the dynamic range that you compute. COUNTA is quite useless in such situations. Since the range is supposed to be of alphanumeric type, use either
=OFFSET(Listing!$I$2,0,0,MATCH(REPT("z",255),Listing!$I:$I)-1,1)
if you don't expect any formula-generated blank cell as the last cell (Mark & Chris, are you taking notice?)
or
=OFFSET(Listing!$I$2,0,0,MATCH(CODE(""""),Listing!$I:$I,-1)-1,1)
if the last cell can house a formula generated blank.
Also worth a mention is that I'm using the dreaded named range in a SUMPRODUCT(), something you've advised me against doing.
I don't recall having committed such a sin. Nothing is wrong with what follows, where Range1 and Range2 are named ranges, even when dynamic:
=SUMPRODUCT((Range1=1)*(Range2="a"))
as long as Size(Range1)=Size(Range2).
BUT! now this does confuse me! using my orignal formula:
=IF(LEN(Listing!I2:I845),SUMPRODUCT(1/COUNTIF(Listing!I2:I845,Listing!I2:I845)))
It works, I'd expect it to. It also works with the two named ranges and the manual range:
=IF(LEN(AddressOfCustomer),SUMPRODUCT(1/COUNTIF(AddressOfCustomer,Listing!I2:I845)))
I always thought that the ranges had to be indentical to work?? The above formula contains the error in the OFFSET() so the ranges in this case are :
=IF(LEN(Listing!I2:I846),SUMPRODUCT(1/COUNTIF(Listing!I2:I846,Listing!I2:I845)))
Any thoughts??
Your observations are right. However, the unequal sized ranges issue does not involve here the SUMPRODUCT function itself, but the COUNTIF function. It's quite right for the COUNTIF to process unequal sized ranges:
Consider
{1;2;3} in E2:E4 and
{1;2;"w";"s"} in F2:F5.
=SUMPRODUCT(COUNTIF(E2:E4,F2:F5))
[ or {=SUM(COUNTIF(E2:E4,F2:F5))} ]
should simply produce 2. It's in the 'nature' of COUNTIF (like MATCH) that it can work with such ranges. SUMPRODUCT pushes COUNTIF here to accept a multicell range as condition and produce a constant array to feed to SUMPRODUCT.
A more important issue is that the formula
=IF(LEN(Range),SUMPRODUCT(1/COUNTIF(Range,Range)))
will not work as intended (something that surfaced up this evening). The idea behind is that you don't get a #DIV/0! when Range has (formula-generated) blanks. I feel guilty about it somehow. The formula must be an array-formula, as I posted it at the newsgroup worksheet.functions a while ago:
=SUM(IF(LEN(Range),1/COUNTIF(Range,Range)))
Please switch to this array-formula.
By the way, the inventor of the original array-formula
=SUM(1/COUNTIF(Range,Range))
is David Hager. The LEN bit is something that I started using at the old board, instead of ISBLANK, in order to circumvent the trouble with formula-generated blanks.
Aladin
This message was edited by Aladin Akyurek on 2002-03-22 10:49