Count If Array Formula

[carl]

I am trying to build an array formula that will count the number of unique
values in Col D subject to the value in ColA="Directed", ColB=60, ColC=285.

Is it possible ?

Thank you in advance.

[Peo Sjoblom]

Although it is possible using a formula it is rather complicated, it's easy
using the advanced filter, assume the table is called MyTable where the
first data in A starts in A12, B in B12 and so on

in let's say G2 put

=AND(A12="Directed",B12=60,C12=285)

leave G1 blank


select the table and do data>filter>advanced filter, select copy to another
location (I prefer that compared to filter in place but you can do that as
well), in the list range type

MyTable

in the criteria range us

$G$1:$G$2

copy to select the cell where you want the new table, select unique records
only and click OK

Now you can just use

=COUNTA(Range)

where Range is the filtered column D without the header


--

Regards,

Peo Sjoblom

"carl" <carl@discussions.microsoft.com> wrote in message
news:872BFC1B-9451-4F29-925F-69B6EACB6271@microsoft.com...
> I am trying to build an array formula that will count the number of unique
> values in Col D subject to the value in ColA="Directed", ColB=60,
ColC=285.
>
> Is it possible ?
>
> Thank you in advance.

[GerryK]

=SUM(IF(FREQUENCY(IF((A1:A100="directed")*(B1:B100=60)*(C1:C100=285),MATCH(D1:D100,D1:D100,0),""),IF((A1:A100="directed")*(B1:B100=60)*(C1:C100=285),MATCH(D1:D100,D1:D100,0),""))>0,1))

(Ctrl + Shift + Enter) in some cell to count for the first 100 records.

HTH
GerryK

"carl" wrote:

> I am trying to build an array formula that will count the number of unique
> values in Col D subject to the value in ColA="Directed", ColB=60, ColC=285.
>
> Is it possible ?
>
> Thank you in advance.