Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values among duplicates without considering specific words
unique values among duplicates without considering specific words from
different columns. Ex; column A Column B 1 abc del 2 abc 3 abc 4 bcd del shud result in 1 (i.e; does not count the data which has del in column B, also no duplicates in Column A) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
unique values among duplicates without considering specific words
Assuming there are no empty cells in column A.
Try this array formula** : =SUM(IF(FREQUENCY(IF(B1:B4<"del",MATCH(A1:A4,A1:A 4,0)),ROW(A1:A4)-ROW(A1)+1),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Simna" wrote in message ... unique values among duplicates without considering specific words from different columns. Ex; column A Column B 1 abc del 2 abc 3 abc 4 bcd del shud result in 1 (i.e; does not count the data which has del in column B, also no duplicates in Column A) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate unique values among duplicates | Excel Worksheet Functions | |||
Duplicates and unique values | Excel Worksheet Functions | |||
Identifying unique values among duplicates | Excel Worksheet Functions | |||
How do I sum unique values among duplicates horizontally | Excel Worksheet Functions | |||
check for duplicates, then sum unique values | Excel Discussion (Misc queries) |