Results 1 to 8 of 8

Thread: Countif

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Countif

    I have 24k rows of data representing 3 months of guest visits and activity. There are 19,073 unique guests, most of which have only one visit, while other guests may have several visits or even several visits each month.

    The business application we use creates a new record or "visit" when a new guest arrives or existing guest returns. The app tracks a guests' visit number which is one of the fields I want to work with in the exported records. I would like a formula that counts the unique visit number and places that count in the first row (record) for that guest in column F. For example, Guest 297218 (rows 32-36) has 5 visits. What would the formula be that counts the five visits (49, 50, 51, 52, 53) in col A for that guest out of the entire range of data?

    I have attached a sample worksheet with sample data.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Part of the solution. If you put this ARRAY formula (ctrl+shift+Enter) in F6 and fill down, you'll count the visits in A.
    But, only part of the solution because the results shows up for each guest number.

    =SUM(IF($B$6:$B$102=B6,$A$6:$A$102,0))

    I haven't thought further about having it show up in only the first entry of the guest number. Maybe someone will.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Well, I think this works (still an array formula), but maybe someone will have a more elegant solution.

    =IF(COUNTIF($B$6:$B$102,B6)=1,A6,IF(COUNTIF($B$6:B 6,B6)=1,SUM(IF($B$6:$B$102=B6,$A$6:$A$102,0)),""))

    Kevin

  4. #4
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi Arcturus16a

    Another approach to kweaver solutions, could be!

    =IF(COUNTIF($B$6:$B6,$B6)>1,"",SUMIF($B$6:$B$102,$ B6,$A$6:$A$102))
    Non array.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Ah, good one, Kevin R.

    Kevin W.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Both of these solutions SUM the number of visits rather than COUNT. In the example I used above Guest # 297218 has five unique visits (sequentially numbered 49, 50, 51, 52, 53). These formulas both generate the sum of the visit counts for a total of 255.

  7. #7
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi

    You want to count the number of times that each guest made a visit. If so, maybe!
    In say I6 and copy down.
    =IF(COUNTIF($B$6:$B6,$B6)>1,"",COUNTIF($B$6:$B$102 ,$B6))

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Yes. Thanks Kevin. That works. I think was hoping to learn of a solution that might use the guest # criteria to sum the unique visit #'s. But since each row (record) represents one visit and there can be only one guest # per record, then might as well just count the rows (of a guest's #).

Posting Permissions

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