Remember Me? November 21st 05, 09:12 PM posted to microsoft.public.excel.worksheet.functions
 carl Posts: n/a Count If Array Formula

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 ? November 21st 05, 09:34 PM posted to microsoft.public.excel.worksheet.functions
 Peo Sjoblom Posts: n/a Count If Array Formula

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 datafilteradvanced 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" wrote in message
...
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 ? November 21st 05, 09:52 PM posted to microsoft.public.excel.worksheet.functions
 GerryK Posts: n/a Count If Array Formula

=SUM(IF(FREQUENCY(IF((A1:A100="directed")*(B1:B100 =60)*(C1:C100=285),MATCH(D1 100,D1 100,0),""),IF ((A1:A100="directed")*(B1:B100=60)*(C1:C100=285),M ATCH(D1 100,D1 100,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 ?

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Brad Excel Worksheet Functions 9 October 17th 05 09:00 PM [email protected] Excel Worksheet Functions 7 August 28th 05 06:46 AM Andrew L via OfficeKB.com Excel Worksheet Functions 2 August 1st 05 02:36 PM Melissa Excel Worksheet Functions 2 July 29th 05 02:37 AM Werner Rohrmoser Excel Worksheet Functions 1 June 22nd 05 12:11 PM

All times are GMT +1. The time now is 03:41 AM. Copyright ©2004-2021 ExcelBanter.