Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Array Formula
=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),M ATCH(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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array | Excel Worksheet Functions | |||
Editing Array Formula | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Count if array contains cells of a certain value | Excel Worksheet Functions | |||
Array Formula, noncontigous range | Excel Worksheet Functions |