Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Counting unique entries with two given conditions

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Bristol, England
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Could anyone help me with the following:

    I have three colums of data.

    In the first colum I have names
    The second Column I have week numbers
    And the 3rd column I have dates

    I have to report how many dates in ta given week number reports were submitted by each person.

    The problem is that on some days more than one report has been submitted so when I count using an array formula such as

    =SUM((A2:A10="Name")*(B2:B10="week number")
    *C2:C10)
    The result is wrong.

    I need to count the number of distinct (unique) dates in the date column (c)for a given person on a given date

    so I need a combination of

    =SUM((A2:A10="Name")*(B2:B10="week number")
    to select the ranges together with

    =SUM(N(FREQUENCY(C1:A10,C1:C10)>0))

    but I'm not sure how to put them together.

    to count the unique entries for the range.

    Or perhaps there is an easier way or another function I could use.

    Any suggestions would be gratefully received.



  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Array-enter:

    =SUM(IF((A2:A5=E1)*(B2:B5=F1),1/COUNTIF(C2:C5,C2:C5)))

    where E1 houses a name and F1 a week number.

    To array-enter a formula, hit control+shift+enter at the same time, not just enter.

    Aladin

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why dont you use a pivot table?

    Name and week in row area, date in data field
    will give you the info in an instant


  4. #4
    Board Regular megnin's Avatar
    Join Date
    Feb 2002
    Location
    Fort Lauderdale
    Posts
    340
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a similar problem. I tried it with a pivit table. It did not give me unique "dates", but counted every occurance.
    How do you tell a pivit table to only count unique occurances?
    "Count of" counts them all.

    megnin@nortelnetworks.com

  5. #5
    Board Regular
    Join Date
    May 2002
    Location
    Bristol, England
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The first solution didn't seem to work - It still counted all the values even though I formatted the dates/time as just a short date. It perhaps only works on whol numbers.

    I finally solved the problem using some code and a pivot table. (Thanks for suggesting this I hadnt used pivot tables before)

    As the du-plicates when they occurred were appearing one after the other I used the following code to replace all the duplicate entries, except the first, in each group with the string "----". To use this code, first select the cells (in one column) that you want to change, then run the procedure.

    Sub FixDuplicateRows()
    Dim RowNdx As Long
    Dim ColNum As Integer
    ColNum = Selection(1).Column
    For RowNdx = Selection(Selection.Cells.Count).Row To _
    Selection(1).Row + 1 Step -1
    If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
    Cells(RowNdx, ColNum).Value = "----"
    End If
    Next RowNdx
    End Sub

    (I got this code from http://www.cpearson.com/excel/duplicat.htm>

    This formed a column with just the unique dates for each week. I then used this column in the pivot table which counted the dates for me.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default


    On 2002-05-16 00:52, rogerm wrote:
    The first solution didn't seem to work - It still counted all the values even though I formatted the dates/time as just a short date. It perhaps only works on whol numbers.


    Here is how:

    Microsoft Excel - aaMultCondUniqCount RogerM.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
    G2==SUM(IF((A2:A5=E2)*(B2:B5=F2),1/COUNTIF(C2:C5,C2:C5)))
    ABCDEFG
    1NameWeek NumDate
    2ann:alert('=WEEKNUM(C2)')>11/2/2002 ann1:alert('{=SUM(IF((A2:A5=E2)*(B2:B5=F2),1/COUNTIF(C2:C5,C2:C5)))}')>1
    3ann:alert('=WEEKNUM(C3)')>11/2/2002
    4bob:alert('=WEEKNUM(C4)')>11/4/2002
    5fred:alert('=WEEKNUM(C5)')>21/7/2002
    Sheet1

    To see the formula in the cells just click on the cells hyperlink

    The above image was automatically generated by [HtmlMaker V1.22]
    If you want this code, click here and Colo will email the file to you.
    This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo



  7. #7
    Board Regular megnin's Avatar
    Join Date
    Feb 2002
    Location
    Fort Lauderdale
    Posts
    340
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My result using the above formul is a fraction. The count of unique items could not be a fraction. I have a worksheet with 42000 rows. I get 901.8174 as the result.

    Order#........Color........Shape
    11A Red Circle
    11A Red Square
    11A Blue Square
    12B Blue Triangle
    12B Red Rectangle
    13C Green Circle
    13C Green Triangle

    I need a formula that will return the number of unique Order #s that contain only red circles or squares and blue circles and squares. The result from the above data would be 1. Only 11A..Red..Circle is unique Order # matching criteria.

    I do not think it can be done with a single formula.

    (Advanced Filters do not return unique numbers, they return every matching Order #)

    Thanks for any assistance.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-16 09:31, megnin wrote:
    My result using the above formul is a fraction. The count of unique items could not be a fraction. I have a worksheet with 42000 rows. I get 901.8174 as the result.

    Order#........Color........Shape
    11A Red Circle
    11A Red Square
    11A Blue Square
    12B Blue Triangle
    12B Red Rectangle
    13C Green Circle
    13C Green Triangle

    I need a formula that will return the number of unique Order #s that contain only red circles or squares and blue circles and squares. The result from the above data would be 1. Only 11A..Red..Circle is unique Order # matching criteria.

    I do not think it can be done with a single formula.

    (Advanced Filters do not return unique numbers, they return every matching Order #)

    Thanks for any assistance.
    Megnin,

    The above formula is appropriate in Rogerm's case that requires AND'ing. Your case requires OR'ing (and AND'ing) for which it will not work. Becasuse you have a huge amount data, a single array formula (if there is one0 would be performance-degrading. That's reason why I looked and suggested a different solution for your case in a different thread.

    Aladin

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting unique entries with two given conditions

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book3___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    XYNumber or letters where Y > 5 = 6
    2
    a1
    3
    a2
    4
    a6
    5
    b6
    6
    b0
    7
    c1
    8
    c2
    9
    c4
    10
    d1
    11
    d2
    12
    d3
    13
    d1
    14
    e6
    15
    e7
    16
    e1
    17
    f8
    18
    f9
    19
    g1
    20
    g2
    21
    h3
    22
    h6
    23
    I5
    24
    i6
    25
    j1
    26
    j2
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

    How can I count the number of Unique X-Values that have at least one Y-value greater than 5?

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Counting unique entries with two given conditions

    Quote Originally Posted by Cosmos75
    ...
    How can I count the number of Unique X-Values that have at least one Y-value greater than 5?
    =COUNTDIFF(IF(B2:B26>5,A2:A26))-1

    which you need to confirm with control+shift+enter, not just with enter.

    Requires the morefunc add-in.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

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
  •