Countif problem

[Werner87]

Hi Guys,

I am stuck with a problem and have hit a brick wall. Will really appreciate some help on this.

I have a data set with multiple store names in. I am trying to count the amount of stores that are in the data set.
What has worked thus far is this count if function that I used that looks like this: =COUNTIF(CONSIGNEE,"*" & "Checkers" & "*") – Consignee is the column in which all the store names appear. However some data sets have multiple names in one line like: "Shoprite Checkers".

Both these stores need to be counted as they are separate stores, but sometimes they the names are interchangeable and thus get typed in as one, but when they appear in the same line they get counted twice even though its one location.

Is there a way to fix this?

[Bob Phillips]

Why don't you use

=COUNTIF(CONSIGNEE,"Checkers")

[dilipandey]

Hi Werner87,

Welcome to the forum.

So do you need to count the names having the word "checkers" in them...?
Or, do you want to count the names, may be in a cell with spaces in between like... "abc def ghi checkers" or may be without checkers.. i.e., "abc xyz ddd" ?

Upload a sample file or a list of those store names with the answer you are looking for.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Werner87]

Hi Bob,

The issue is I am trying to count the amount of stores that are in the data set that match the chain stores that we deliver to already. The goal is to only count certain store names that we make deliveries to and so I can determine the synergies between the data set and our own deliveries.
The normal =COUNTIF(CONSIGNEE,"Checkers") doesn’t work as the store names are not always typed in the same way and are mostly surrounded by locations names or other parts of text strings. An example is: “SHOPRITE CECKERS MIDRAND DC - PRETORIA”.

[Werner87]

Hi DILIPandey,

I am trying to count the names that are in cells with spaces in them. I uploaded an example of what I’m trying to do.

Thanks
Werner87

[Fotis1991]

Hi and welcome to the forum.

This Array formula(CSE), gives results but i am not able to test these(Huge range). Pls, take a look.

=SUM(LEN($D$2:$D$65000)-SUM(LEN(SUBSTITUTE($D$2:$D$65000;A2;"")))/LEN(A2))

Change the semi colons to comma, if you have to do this..

[Werner87]

Hi Fotis,

I tried your formula, however it returns a decimal answer were it is suppose to have a value of 0.

Any other ideas?

Regards
Werner

[Fotis1991]

Hi

Are the numbers in colomn B, the correct result??

If NO, pls, give me an example..

[Werner87]

Hi,

The numbers in column B are correct. The only issue is with the Shoprite store name and the Checkers store name as they are counted twice but is suppose to be counted as one for when they are in the same line.

Werner

[Haseeb Avarakkan]

Hi,

In B2, then copy down.

=SUMPRODUCT(ISNUMBER(SEARCH(" "&A2&" "," "&D$2:D$1445&" "))+0)

Is that you wish to get?