Array Formula

[Gatochi]

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

 

[willwonka]

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

 

[Gatochi]

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:

 

[Bob Phillips]

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

=SUM(IF(FREQUENCY(IF((A2:A12<>"")*(D212=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.

--
HTH

Bob Phillips

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

 

[Bernie Deitrick]

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

 

[Gatochi]

Thanks All of you!


Bernie Deitrick ha escrito:

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