Results 1 to 11 of 11
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I have a worksheet with a range named "Email". The range contains about 500 email addresses. I want to get a count of all those that end in "hilton.com"

    I've tried using COUNTIF, and I can't come up with the right argument. Maybe COUNTIF won't do the job, or maybe I'm not on the right track with the arguments. Nevertheless, I want to count those hilton.coms.

    Who can help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Have you tried filtering. If your email range is in one column, then on that column use the custom filter " contains" and "hilton.com".

    If you have a column containing numbers in the data, you could block the filtered column and the sum should appear on the botton of the spreadsheet. Right click the sum and change the operation to count.

    Thas should give you the number.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    Here's a VBA UserDefined Function that will do the trick. You can modify it as you see fit.
    Call it as =lsubstrcountif("RangeName","Substr to find")
    In your case: =lsubstrcountif("Email","Hilton.com")

    Hope this helps.

    RG
    VBA Rules!
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    These are very fine solutions - if you do require a formula based solution, I would use the following:
    Assuming your email addresses are in Column A, rows 2 - 500.
    {=sum((right(a2:a500,len(a2:a500)-find("@",a2:a500))="hilton.com")*1)}

    since this is an array formula, you will need to enter it with the Ctrl + Shift + Enter key combination. You can't type in the {} brackets.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Here is another solution that can count any of the domains (holiday.com, hilton.com, yahoo.com, etc.).

    See attached sample.
    In Cell E2 - Type the name of the domain to be counted.
    Attached Files Attached Files

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I agree that they ARE very fine solutions.

    The one from Capri suits my needs perfectly. I only need to get my count from time to time, and Capri's method is an easy and quick way to get it.

    I use Autofilter all the time, but had been unaware of the Custom option (or at least had never used it). Capri's suggestion, augmented by Excel Help's very good description of how to do a Custom filter, has yielded exactly what I need.

    With a few clicks and a bit of typing, I can filter the list to show the hiltons. I select the entries in the Email column, and the count appears on my Status bar.

    Thanks to all for their help.

    I have two questions:

    1) What ever happened to the guru Hans, who formerly hung out here? (I'm assuming he moved on when the site became "Windows Secrets Lounge."

    2) How can I help this site financially?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Just My 2 Pence worth

    You CAN use wild cards with COUNTIF

    So you could use

    =Countif(RangeToCount,"*hilton.com")

    [attachment=88947:CountifWildCard.jpg]

    [attachment=88948:CountifWildCard.xls]

    Has the advantage of being dynamic rather than needing to filter
    Also you can use more than one wild card so IF for example Hilton.Com is embedded you can use

    "*hilton.com*" as your criteria
    Attached Images Attached Images
    Attached Files Attached Files
    Andrew

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Once again I'm amazed at the depth of knowledge available here! It's amazing to me how every poster here approached this problem from a different angle and with different skill sets. I've picked up three new ways to solve problems.

    Thanks to Catherine's post I think I'm finally getting my head wrapped around array formulas. I couldn't get her's to work (probablly a mis-type somewhere on my part: =SUM((RIGHT(Email,LEN(Email)-FIND("@",Email))="Hilton.com")*1)) but I did get a variation to work: =SUM((RIGHT(Email,10)="Hilton.com")*1)

    Thanks all.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Glad to be of service - its' funny how problems come in clusters, I was just answering a question that required a different variation of that array formula.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    AKW's reply is even more perfect than the other one. In fact, it shows a solution that is exactly what I thought I might be looking for in the first place.

    I've been a PC owner, user, and programmer since 1979. I'm still amazed at the way very simple solutions emerge after long, wonderful, and intricate endeavors. In the early days, it wasn't uncommon for 20 lines of BASIC to be do-able in one or two lines, once the details were worked out and the 20-line solution had been found.

    It's like you get from Point A to Point B by hacking your way through a jungle full of unexplored hills and rivers. Once you're there, you see how you could have done it by bending a few bushes.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, since you asked, Hans is currently answering all the questions at Eileen's Lounge.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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