Results 1 to 7 of 7

COUNTIFS FOR Unique Values based on Months while reading from dates

Threaded View

  1. #1
    Registered User
    Join Date
    07-09-2011
    Location
    Manheim
    MS-Off Ver
    Excel 2010
    Posts
    23

    COUNTIFS FOR Unique Values based on Months while reading from dates

    Hi all,

    I have been for a long time trying to fix a tricky issue without any solution . I have applied a series of array formulas without success.

    I have the column B (B2:B300000), Name: Month, Format: mmm-yy (This is the column with the Months)
    I have the Column I (I2:I300000), Name: Type, Format: It includes both text and numbers. (This is the column with the types)
    I have the Column T (T2:T300000), Name: Names, Format: Only characters. (This is the column with the names)
    I have the Column H (H2:H300000), Name: Date happened, Format: dd/mm/yyyy. (This is the Column with the dates happened each type in Column I)

    I have a reference cell: B16 --> This is the reference for the Month (its format is: mmm-yy)
    I have a reference cell: A17 --> This is the reference cell for the Name (its format is: only characters)

    I want to create a function (I do not know whether COUNTIFS, SUMIF, COUNT is best) where:

    For the reference name and the reference month, to count the monthly unique number of types BASED ON the Dates happened (of the month in the reference cell).

    For instnace, in date happened I may have lets say 33 types where some of them are duplicates. So, the formula should compute the unique values of

    Here is a small example of my tricky intro:

    The raw data are below:

    name_column// date_happened// type_column//
    NAME, 03/06/2009, LA
    03/06/2009 LA
    05/06/2009 LA1
    11/06/2009 LA
    13/06/2009 LA
    13/06/2009 LA
    13/06/2009 LA
    16/06/2009 LA
    17/06/2009 LAN
    18/06/2009 LA
    18/06/2009 LA
    19/06/2009 LA1
    20/06/2009 LAA
    20/06/2009 LAT1
    20/06/2009 LAA
    24/06/2009 LA
    24/06/2009 LAT
    25/06/2009 LA
    26/06/2009 LA
    26/06/2009 LAA
    27/06/2009 LAK
    31/06/2009 LA
    LAT


    (the column here has only }
    the unique values of types) } The unique number I want to get from the formula is 18 for the Month: Jun-09 (Ref. Cell B16)

    name_column// date_happened// type_column//
    NAME, 03/06/2009, LA
    05/06/2009 LA1
    11/06/2009 LA
    13/06/2009 LA
    16/06/2009 LA
    17/06/2009 LAN
    18/06/2009 LA
    19/06/2009 LA1
    20/06/2009 LAA
    20/06/2009 LAT1
    24/06/2009 LA
    24/06/2009 LAT
    25/06/2009 LA
    26/06/2009 LA
    26/06/2009 LAA
    27/06/2009 LAK
    31/06/2009 LA
    31/06/2009 LAT

    Any help, would be very appreciated. Thank you very much
    Last edited by dim06; 02-27-2012 at 09:46 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1