Results 1 to 3 of 3

Count unique text values based on condition in another column

This is a discussion on Count unique text values based on condition in another column within the Excel Questions forums, part of the Question Forums category; I want to create a summary from the following table : Date Name Location 19-feb John DS1 19-feb John DS1 ...

  1. #1
    New Member
    Join Date
    Feb 2014
    Posts
    6

    Default Count unique text values based on condition in another column

    I want to create a summary from the following table :

    Date Name Location
    19-feb John DS1
    19-feb John DS1
    19-feb John DS2

    By adding a condition [if John made 3 observation cards at the same day , then each unique site will be considered as 1 site visit] so at the end it will look like the following table :

    Name
    No. Of cards For February
    John
    2
    William
    1
    Mark
    2

    The register I am using for one year and have several sheets each sheet reflect a particular month , any idea whether itís a formula to calculate this or a macro it will be great as long as it does the job
    Appreciate any help

  2. #2
    Board Regular
    Join Date
    Nov 2012
    Location
    Sydney, Australia
    Posts
    1,626

    Default Re: Count unique text values based on condition in another column

    Have you thought about using a Pivot Table? You will probably need one for each sheet.

  3. #3
    Board Regular dispelthemyth's Avatar
    Join Date
    Mar 2006
    Location
    England
    Posts
    599

    Default Re: Count unique text values based on condition in another column

    Add a helper column at the end as below


    Sheet1

    * A B C D
    1 Date Name Location Helper column
    2 19/02/2014 John DS1 0.33
    3 19/02/2014 John DS1 0.33
    4 19/02/2014 John DS2 0.33
    5 19/02/2014 Joan DS1 0.50
    6 19/02/2014 Joan DS2 0.50
    7 20/02/2014 Joan DS3 1.00
    8 19/02/2014 Peter DS2 1.00
    9 20/02/2014 Peter DS1 0.50
    10 20/02/2014 Peter DS2 0.50

    Spreadsheet Formulas
    Cell Formula
    D2 =1/COUNTIFS(B:B,B2,A:A,A2)
    D3 =1/COUNTIFS(B:B,B3,A:A,A3)
    D4 =1/COUNTIFS(B:B,B4,A:A,A4)
    D5 =1/COUNTIFS(B:B,B5,A:A,A5)
    D6 =1/COUNTIFS(B:B,B6,A:A,A6)
    D7 =1/COUNTIFS(B:B,B7,A:A,A7)
    D8 =1/COUNTIFS(B:B,B8,A:A,A8)
    D9 =1/COUNTIFS(B:B,B9,A:A,A9)
    D10 =1/COUNTIFS(B:B,B10,A:A,A10)


    Excel tables to the web >> Excel Jeanie HTML 4



    Then add a pivot table to it and put

    the name in the row field
    Date in the column field and
    the helper column in the value/data field


    In the column field you will likely have loads of dates for each month you are doing it for, to make this just be a month

    select on of the sates on the pivot table and on the ribbon you should see pivot tools at the end, select options then group selection and select month (put in an appropriate start and end date)


    you will get something like the below


    Sheet4

    * A B C
    1
    2
    3 Sum of Helper column Column Labels
    4 Row Labels Feb
    5 Joan 2
    6 John 1
    7 Peter 2
    8
    9
    10


    Excel tables to the web >> Excel Jeanie HTML 4
    Excel 2007/2010 Windows 7

    I've always listening so i'm always learning


    Before posting your question, reread it to make sure you understand it.

    If possible post example data to make it easier to help you

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com