Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula for unique values
I should have mentioned that Range2 could also contain non-numeric values.
Sorry. Thanks though. "Teethless mama" wrote: =SUMPRODUCT(--($A$2:$A$100=C2),--($B$2:$B$100=D2) C2 holds AA D2 holds 4 C3 holds AB D3 holds 2 and so on "Dan Hatola" wrote: 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) |