Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 21st 05, 08:12 PM posted to microsoft.public.excel.worksheet.functions
carl
 
Posts: n/a
Default 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 ?

Thank you in advance.

  #2   Report Post  
Old November 21st 05, 08:34 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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 ?

Thank you in advance.



  #3   Report Post  
Old November 21st 05, 08:52 PM posted to microsoft.public.excel.worksheet.functions
GerryK
 
Posts: n/a
Default Count If Array Formula

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Brad Excel Worksheet Functions 9 October 17th 05 09:00 PM
Editing Array Formula [email protected] Excel Worksheet Functions 7 August 28th 05 06:46 AM
Array Formula - using LEFT("text",4) in formula Andrew L via OfficeKB.com Excel Worksheet Functions 2 August 1st 05 02:36 PM
Count if array contains cells of a certain value Melissa Excel Worksheet Functions 2 July 29th 05 02:37 AM
Array Formula, noncontigous range Werner Rohrmoser Excel Worksheet Functions 1 June 22nd 05 12:11 PM


All times are GMT +1. The time now is 12:30 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017