Interesting Sumproduct/Counta Behavour??

[Ian Mac]

I have the following formula is return the number of unique entries in a list:

=IF(LEN(Listing!I2:I845),SUMPRODUCT(1/COUNTIF(Listing!I2:I845,
)))

Which works fine:

the problem being the list varies in length, so I've added dynamic ranges to the formula:

=IF(LEN(Address),SUMPRODUCT(1/COUNTIF(Address,Address)))

Where 'Address' is =OFFSET(Listing!$I$2,0,0,COUNTA(Listing!$I:$I),1).

but now the formula doesn't work, BUT if I change the formula to:

=IF(LEN(Address),SUMPRODUCT(1/COUNTIF(Address,Listing!I2:I845)))

where the last arg in the COUNTA isn't the named range it works.

Any idea??

[Aladin Akyurek]

On 2002-03-22 03:16, Ian Mac wrote:
I have the following formula is return the number of unique entries in a list:

=IF(LEN(Listing!I2:I845),SUMPRODUCT(1/COUNTIF(Listing!I2:I845,
)))

Which works fine:

the problem being the list varies in length, so I've added dynamic ranges to the formula:

=IF(LEN(Address),SUMPRODUCT(1/COUNTIF(Address,Address)))

Where 'Address' is =OFFSET(Listing!$I$2,0,0,COUNTA(Listing!$I:$I),1).

but now the formula doesn't work, BUT if I change the formula to:

=IF(LEN(Address),SUMPRODUCT(1/COUNTIF(Address,Listing!I2:I845)))

where the last arg in the COUNTA isn't the named range it works.

Any idea??

Hi Ian,

I just checked and as I expected, no probs. Although, like you I used 'address' as name, I think we should avoid doing that, that being a function name in Excel's name space. Again, I didn't encountered any trouble.

BTW, what formula did you use to define 'address' as dynamic range name?

[Mark W.]

I'd wouldn't recommend that you use a range as the 2nd argument (criteria) of the COUNTIF worksheet function...

COUNTIF(Address,Address)

The Help topic for COUNTIF specifies that the criteria should be "a number, expression, or text that defines which cells will be counted". And, provides no examples using a cell range or named range.

Upon further reflection... and a much needed 1st cup of coffee of retract my previously stated concerns!

[ This Message was edited by: Mark W. on 2002-03-22 07:22 ]

[Ian Mac]

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)).

Also worth a mention is that I'm using the dreaded named range in a SUMPRODUCT(), something you've advised me against doing.

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??

Mark, I agree with your comments regarding ranges but the formula to return unique number of values/entries requires the use of COUNTA(Range,Range) and I don't see that it makes a difference if I use a named range, the range or indeed just the OFFSET() function for both arguements.

[Mark W.]

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)

Ian, I've found that it's quite useful to check the correctness of a named range by clicking in the "Refers to:" field after the defined name has been added. A marquee (sometimes referred to as "marching ants") should be shown around the desired range. This might have alerted you to the missing -1.

[ This Message was edited by: Mark W. on 2002-03-22 08:11 ]

[Aladin Akyurek]

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 ]

[Chris Davison]

Always

[trillicomm]

On 2002-03-22 07:43, Mark W. wrote:

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)

Ian, I've found that it's quite useful to check the correctness of a named range by clicking in the "Refers to:" field after the defined name has been added. A marquee (sometimes referred to as "marching ants") should be shown around the desired range. This might have alerted you to the missing -1.

[ This Message was edited by: Mark W. on 2002-03-22 08:11 ]

Hello Mark,

This is beside the Excel issue. Do you do private consulting in Austin, TX? I just have to ask you that because I live in Austin, too. Please let me know.

T. Le