Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,180
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Sort by Count the Max nos of Unique text values in Pivot Ta ToExcelAtExcel Excel Discussion (Misc queries) 1 November 7th 06 08:45 AM
I Need to Count Number of Entries Based on Two Criteria Jones Excel Worksheet Functions 3 July 14th 05 10:34 PM
Filter Count of Records Retrieved. amkazen Excel Discussion (Misc queries) 2 March 31st 05 10:03 PM
SUMPRODUCT Formula to Count Row of data Below Matched Criteria Sam via OfficeKB.com Excel Worksheet Functions 8 February 3rd 05 01:37 AM
Count rows based on multiple criteria Murph Excel Worksheet Functions 1 October 28th 04 07:13 AM


All times are GMT +1. The time now is 11:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"