Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,

    I need help with a calculation to calculate how many "individuals" attended which courses. I have attached a spreadsheet with the sample data. TIA for your help.
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can do this in two steps: Advanced Filter to extract unique records, and a pivot table to calculate the counts. See this recent topic.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    That will work on the attached (modified) file, but the prob is that the records on the original source file have additional fields of data that will not allow an advanced unique filter. I was thinking in line of a formula (or array) formula to determine a count for unique person/subject count.

    Any ideas?
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't have to include all column headings in the Extract to range for Advanced Filter, you should only use those needed to get the unique records.

    See the attached version: [attachment=86305:Subject Count.xls]
    Attached Files Attached Files

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks for the advice. I'll give it a go on the original.
    Appreciate the help
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Another way to do it is by creating an auxiliary column in the source table.
    The formula in G2 is =--(SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2))<2), this can be filled down.
    This returns 1 for the first occurrence of an Id-Number for a specific Subject Description, 0 otherwise.
    You can then use SUMIF formulas or a pivot table to count the unique Id-Numbers for each Subject Description.
    With SUMIF, the formula in J8 is =SUMIF($F$2:$F$349,I8,$G$2:$G$349), this can be filled down.

    See attached version: [attachment=86311:Subject Count2.xls]

    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Rudi View Post
    ........I was thinking in line of a formula (or array) formula to determine a count for unique person/subject count.
    Another way, wihout using helper column,

    Cell J8, enter the formula and copy down :

    =SUMPRODUCT((F$2:F$349=I8)*(MATCH(F$2:F$349&A$2:A$ 349,F$2:F$349&A$2:A$349,)=ROW(F$2:F$349)-1))

    Regards
    Bosco

Posting Permissions

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