Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique records based on the Criteria in another colum
I want to count the unique entry (Text & Numeric) based on the condition on
another corresponding row data, following is an example of data set any Rajat why Two who Three how 5 when Rajat any 4 why Two any Rajat any 5 when Rajat i want to count perticular value in colum A has how many unique entry in Colum B. e.g. "any" has how many unique entry in Colum B Answer is 3 (any has value of Rajat, 4, Rajat, 5) In this case what formula should i use, any suggestion will be of great help to me. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique records based on the Criteria in another colum
You can also use Pivot Table and a simple formula:
Assume your two columns have headers An and Bn. Pivot Table Options: Uncheck grand totals. Layout: Row = An, Column = Bn, Data = Count of Bn The PT will look like this: An 4 5 Rajat Three Two Unique any 1 1 2 3 how 1 1 when 2 1 who 1 1 why 2 1 Select "An" thru "why" ( 6x1 cells) and Insert Name Create Top Row Select "any" thru "2" ( 5x6 cells) Insert Name Create Left Column The "Unique" column has been added next to the PT. "Unique" has this formula: =COUNTA(INDIRECT(An)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Sort by Count the Max nos of Unique text values in Pivot Ta | Excel Discussion (Misc queries) | |||
I Need to Count Number of Entries Based on Two Criteria | Excel Worksheet Functions | |||
Filter Count of Records Retrieved. | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |