Question for Excel gurus here

PhilKing

You can use countifs to have 2 conditions, one is the current one you have and the other is 123. (with the full stop).  

LionAquaLooper

stickyfiddle said:

LionAquaLooper said:

Sporky said:

I'm really not sure what you're after.

You have lots of strings, some of which have a full stop delimiter in them?

You want to count how many of them begin with another string? 

Is the string you want to count actually "123", or do you want to enter a string of arbitrary length in a cell and get an answer? 

If they do not have a delimiter, you want to count them if they exactly match the other string? 

If they do have a delimiter, they should only be counted if the delimiter is immediately after the other  string?

Hopefully this illustrates it better:



I just put the expected results in column H but that's where I want to put a formula in.

What is your logic that would mean 123.xyz would be counted but 1234 or 1234.xyz wouldn't be? 

What about 123xyz? 

My data is basically server names and domains, but I simplified it to letters and numbers because I can't obviously divulge this type of data, and easier to just simplify to short letters and numbers.

But suppose I'm searching for server name "TheFretBoard" in my list of servers.  This can either exist as just "TheFretBoard" or "TheFretBoard.google.com" (depends on a lot of technical things).  So basically if I'm searching a list of thousands of servers, just doing a COUNTIF on "TheFretBoard" won't be good enough because it could exist as "TheFretBoard.google.com" - which is actually a match.  

So to answer your question, this is why 123xyz shouldn't be counted because that's like saying "TheFretBoardgoogle.com".   .

DuploLicks

If you know that it’s always the same string, e.g. “thefretboard” wouldn’t you just use wildcards?

countif(a1:a100,”*thefretboard*”)

notanon

If you can define what you are looking for I'm sure ChatGPT will solve that. 

I specified 

In Excel in column A there is a list of domains such as my domain.com or www.mydomain.com or mydomain.google.com.

In column C there is a list of the parts of the domains I want to count if exists in column A and put the number in column E.

If cell C1 is my domain that will match all domains in A. So E1 should be 3.

If mydomaingoogle.com exists in A that should not be counted because mydomain isn't a subdomain and it isn't a domain that matches mydomain such as mydomain.com or mydomain.net. etc

ChatGPT came back with

To count the number of times a specific domain or its subdomains (but not other domains containing the same string as a substring) appear in column A, you can use Excel formulas with a combination of `SEARCH`, `IF`, and `SUMPRODUCT`.

Here's the step-by-step solution:

### Step 1: Normalize domains in Column A

1. **In Column B**, create a helper column to normalize the domains to remove the "www." part and to ensure consistent format.

   In cell `B1`, enter the following formula and drag it down:

   ```excel

   =IF(LEFT(A1, 4) = "www.", MID(A1, 5, LEN(A1)-4), A1)

   ```

### Step 2: Create a matching formula in Column E

2. **In Column E**, enter a formula to count the matches based on the criteria provided in Column C.

   In cell `E1`, enter the following formula and drag it down:

   ```excel

   =SUMPRODUCT(--(ISNUMBER(SEARCH(CONCATENATE(C1, "."), B:B))))

   ```

### Explanation:

1. **Normalization in Column B**:

   - `IF(LEFT(A1, 4) = "www.", MID(A1, 5, LEN(A1)-4), A1)` checks if the domain starts with "www.". If it does, it removes the "www." part. Otherwise, it leaves the domain unchanged.

2. **Matching formula in Column E**:

   - `SEARCH(CONCATENATE(C1, "."), B:B)` searches for the string `C1` followed by a period in Column B. This ensures that only exact domains or subdomains of `C1` are matched.

   - `ISNUMBER(SEARCH(CONCATENATE(C1, "."), B:B))` returns an array of `TRUE` (1) and `FALSE` (0) values where the search string is found.

   - `SUMPRODUCT(--(ISNUMBER(SEARCH(CONCATENATE(C1, "."), B:B))))` counts the number of `TRUE` values, giving the total number of matches.

### Example:

Given the list in Column A:

```

mydomain.com

www.mydomain.com

mydomain.google.com

anotherdomain.com

sub.mydomain.com

```

And the list in Column C:

```

mydomain

anotherdomain

```

The formulas in Column E would provide the following counts:

```

3 (for mydomain)

1 (for anotherdomain)

```

This method ensures that only the specified domain or its subdomains are counted, excluding other domains that contain the substring incidentally.

Does that work as expected?

LionAquaLooper

I will try this tomorrow when I log in to work again. I was trying to avoid ChatGPT but you've done it now  

LionAquaLooper

DuploLicks said:

If you know that it’s always the same string, e.g. “thefretboard” wouldn’t you just use wildcards?

countif(a1:a100,”*thefretboard*”)

Yea the * wildcard was one of the first things I thought of as well but the risk is if there's "TheFretBoardX" or whatever somewhere in the list. That's a completely different server but the formula will think it's a match to "TheFretBoard". 

crosstownvamp

Effort expended on Fretboard to solve in Excel = n

Effort to write a php script  = n /4