COUNTIF Question

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

Just My 2 Pence worth

You CAN use wild cards with COUNTIF

So you could use

=Countif(RangeToCount,”*hilton.com”)[/size]

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

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

FYI, since you asked, Hans is currently answering all the questions at Eileen’s Lounge.

Reply | Quote