Array Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gatochi - 27 Jul 2006 15:09 GMT

Hi,
I need a formula to count disctints like =
SUM(1/COUNTIF(C26:C46,C26:C46) but I need conditionals in the range and
criteria of countif.
I've tried this, but its not working.
=+SUM(1/COUNTIF(IF(K26:K46=C23,C26:C46),IF(K26:K46=C23,C26:C46))-1)
Thanks

Reply to this Message

willwonka - 27 Jul 2006 15:18 GMT

I'm a little confused here as it seems that the answer would always be
1.

Anyway, perhaps SUMPRODUCT might work for you:

SUMPRODUCT((K26:K46=C23)*(C26:C46)*(1))

> Hi,
> I need a formula to count disctints like =
[quoted text clipped - 3 lines]
> =+SUM(1/COUNTIF(IF(K26:K46=C23,C26:C46),IF(K26:K46=C23,C26:C46))-1)
> Thanks

Reply to this Message

Gatochi - 27 Jul 2006 15:28 GMT

Thanks Will
The answer will be one to each disctint value in the array so the sum
will be the quantity of disctint values.
Also they are not numeric values, they are text strings.
Thanks.

willwonka ha escrito:

> I'm a little confused here as it seems that the answer would always be
> 1.
[quoted text clipped - 10 lines]
> > =+SUM(1/COUNTIF(IF(K26:K46=C23,C26:C46),IF(K26:K46=C23,C26:C46))-1)
> > Thanks

Reply to this Message

Bob Phillips - 27 Jul 2006 15:39 GMT

Here is an example that counts unique A values where D = 16 and G = "BRY"

=SUM(IF(FREQUENCY(IF((A2:A12<>"")*(D2:D12=16)*(G2:G12="BRY"),MATCH(A2:A12,A2

:A12,0)),ROW(INDIRECT("1:"&ROWS(A2:A12))))>0,1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Thanks Will
> The answer will be one to each disctint value in the array so the sum
[quoted text clipped - 18 lines]
> > > =+SUM(1/COUNTIF(IF(K26:K46=C23,C26:C46),IF(K26:K46=C23,C26:C46))-1)
> > > Thanks

Reply to this Message

Bernie Deitrick - 27 Jul 2006 15:43 GMT

Gatochi,

You need a helper column of formulas:

=IF(K26 = $C$23,C26,"")

and then use the array formula:

=SUM(IF(E26:E46<>"",1/COUNTIF(E26:E46,E26:E46),0))

Assuming your helper column of formulas is column E....

HTH,
Bernie
MS Excel MVP

> Thanks Will
> The answer will be one to each disctint value in the array so the sum
[quoted text clipped - 18 lines]
>> > =+SUM(1/COUNTIF(IF(K26:K46=C23,C26:C46),IF(K26:K46=C23,C26:C46))-1)
>> > Thanks

Reply to this Message

Gatochi - 27 Jul 2006 15:50 GMT

Thanks All of you!

Bernie Deitrick ha escrito:

> Gatochi,
>
[quoted text clipped - 34 lines]
> >> > =+SUM(1/COUNTIF(IF(K26:K46=C23,C26:C46),IF(K26:K46=C23,C26:C46))-1)
> >> > Thanks

Reply to this Message

Rate this thread: