Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula for unique values
Worked like a charm. Thanks.
Dan "T. Valko" wrote: Try this: D1 = AA D2 = AB D3 = AC Array entered in E1: =SUM(N(FREQUENCY(IF(A$1:A$12=D1,MATCH(B$1:B$12,B$1 :B$12,0)),MATCH(B$1:B$12,B$1:B$12,0))0)) Copy down to E3 Biff "Dan Hatola" wrote in message ... I am trying to create an array formula that will allow me to count the unique values in Range2 if the value in Range1 matches a given value. Normally, I would use an array formula like =sum(1/countif(Range2,Range2)) to count uniques however when I tried to add the conditional component to it, I got a #VALUE! error. Here is the array formula I tried: =sum(1/countif(if(Range1="AA", Range2),if(Range1="AA", Range2))) Range1 Range2 AA 1 AA 2 AA 3 AA 4 AB 1 AB 1 AB 2 AB 2 AC 1 AC 2 AC 3 AC 2 Below is the output I would like to create (one formula next to each set of letters): AA 4 AB 2 AC 3 Any help would be greatly appreciated. Dan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula for unique values
You're welcome. Thanks for the feedback!
Biff "Dan Hatola" wrote in message ... Worked like a charm. Thanks. Dan "T. Valko" wrote: Try this: D1 = AA D2 = AB D3 = AC Array entered in E1: =SUM(N(FREQUENCY(IF(A$1:A$12=D1,MATCH(B$1:B$12,B$1 :B$12,0)),MATCH(B$1:B$12,B$1:B$12,0))0)) Copy down to E3 Biff "Dan Hatola" wrote in message ... I am trying to create an array formula that will allow me to count the unique values in Range2 if the value in Range1 matches a given value. Normally, I would use an array formula like =sum(1/countif(Range2,Range2)) to count uniques however when I tried to add the conditional component to it, I got a #VALUE! error. Here is the array formula I tried: =sum(1/countif(if(Range1="AA", Range2),if(Range1="AA", Range2))) Range1 Range2 AA 1 AA 2 AA 3 AA 4 AB 1 AB 1 AB 2 AB 2 AC 1 AC 2 AC 3 AC 2 Below is the output I would like to create (one formula next to each set of letters): AA 4 AB 2 AC 3 Any help would be greatly appreciated. Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a range of values in a range of cells | Excel Worksheet Functions | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
lookup a list of values for an array formula | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
array formula values | Excel Discussion (Misc queries) |