Count Values in Cells

Something like this

For including those countries which have some prefix like North or South in your result, change your formula to =COUNTIF($A$2:$A$100,"*"&"?"&C$1&"*").
If you just want to see country names without such prefixes, use your original formula and reduce the result of this modified formula from this. You can do the same thing for suffixes.

philb99 said:

I have a list of countries recorded in Cells from A2 to A1000, some cells have 4 or more countries recorded, and I want to Count certain countries

I have been using this Formula where I have the country that I am looking for entered into Cell C1

=COUNTIF($A$2:$A$100,"*"&C$1&"*")

What it doesn't do is separate those countries with some of the same name like Sudan and South Sudan also North Korea and South Korea

Therefore can any on help with the formula

Click to expand...

See http://www.mrexcel.com/forum/excel-questions/781938-look-up-values-column-return-where-matched.html

where the following formula is used:

=SUMPRODUCT(ISNUMBER(SEARCH(","&C$1&",",","$A$2:$A$100&","))+0)

Caveat. Target strings separate countries with a comma followed by a space.

thank you but you have added 2 different countries together - All of the Sudan's and all of the Koreas

philb99 said:

thank you but you have added 2 different countries together - All of the Sudan's and all of the Koreas

Click to expand...

I suppose this is addressing my proposal. If it does, in which way? Also, try to put all possible issues with input at once if possible, note piecemeal.

By the way, the formula I meant to post is:

=SUMPRODUCT(ISNUMBER(SEARCH("#"&C$1&"#","#"&SUBSTITUTE($A$2:$A$100,", ","#")&"#"))+0)

See your original thread...

Thanks but this formula does not bring back the correct numbers

I don't know if that is possible. It would of course be extremely simple if you had several columns, and only one country entered per column. However, if you want to see all the data neatly, say on a printout, that would give a messy result such as (alphabetically):

Argentina North Korea South Korea Zimbabwe

This can be solved by creating a print range. Enter the countries in 4 columns outside of that range.
Then, in the print range, enter a formula such as:

=A1&IF(ISBLANK(B1),"",", "&B1&IF(ISBLANK(C1),"",", "&C1)&IF(ISBLANK(D1),"",", "&D1))

Result:

Argentina, North Korea, South Korea, Zimbabwe

There is a pitfall, that if a leading or trailing space is entered in the data columns, you will see it in the result column. Thus, if the data column containing South Korea is actually entered as "South Korea" and a SPACE, the result will be:

Argentina, North Korea, South Korea , Zimbabwe

If you are worried about such possibility, you can prevent it with the use of the TRIM function:

=TRIM(A1)&IF(ISBLANK(B1),"",", "&TRIM(B1)&IF(ISBLANK(C1),"",", "&TRIM(C1))&IF(ISBLANK(D1),"",", "&TRIM(D1)))

I tested this by entering South Korea 3 ways:

SPACE and "South Korea"
"South" TWO SPACES "Korea"
"South Korea" and SPACE

Excel was smart enough to give the right result each time:

Argentina, North Korea, South Korea, Zimbabwe

In the case that your purpose is not printing, but merely to be seen neatly on the screen, then I would suggest putting your data entry range on a separate sheet of the workbook.

Hope this helps.

Robyn

Aladin - my reply was to Momentman - your formula is missing something possible an & but dont know where

philb99 said:

Aladin - my reply was to Momentman - your formula is missing something possible an & but dont know where

Click to expand...

You should have stayed in your original thread, where I introduced the following formula

=SUMPRODUCT(ISNUMBER(SEARCH("#"&C$1&"#","#"&SUBSTITUTE($A$2:$A$100,", ","#")&"#"))+0)

for counting.