text addition

[Jay]

how can i add text as numbers, example Washinton has been
entered in the a colum 12 boston was entered 15 times and
i want to keep a record on how many times each has been
entered on a different sheet

 

[Peo Sjoblom]

=COUNTIF(Your_Range,"Washington")

note that it is easier if you replace Washington by a cell reference

=COUNTIF(Your_Range,D1)

where D1 holds Washington

 

[Karen]

if you make A1 say Washington on Sheet2, and A2 say
Boston, you can enter the following equation into B1 and
fill it down for the other cities:

=A1&" was entered "&COUNTIF(Sheet1!$A$1:$A$48,A1)&" times"

(change the text as you see fit - I assumed the sheet with
your data was called Sheet1.

Hope that helps and good luck,
Karen

 

[Jay]

-----Original Message-----
=COUNTIF(Your_Range,"Washington")

note that it is easier if you replace Washington by a cell reference

=COUNTIF(Your_Range,D1)

where D1 holds Washington

--

Regards,

Peo Sjoblom




this is an example i tried the 2 replies but could not

get them to work any more help would be great thank you
jay

.
Boston 1

Washington 4

Washington 4
Montreal 3ot

Washington 3
Boston 4

Boston 1
Montreal 2

Washington 3
Montreal 1

Boston 2
Washington 3

Montreal 5
Washington 2

Boston 1
Montreal 4

Washington 1
Montreal 3

 

[Peo Sjoblom]

Use Harlan's solution with the wildcards (*Washington*)
or add wildcards to the cell

=COUNTIF(Range,"*"&D1&"*")

 

[Ken Wright]

You could firstly use Data / Filter / Advanced Filter / Copy to another location & Unique values
only to get yourself a unique list of all the places. Then just use one of the other solutions
listed, ie assuming your unique list was now in A1:A25 on a new sheet, and your source data was on
Sheet1

Put the following in cell B1 on your new sheet and copy down to B25

=COUNTIF(Sheet1!$A$1:$A$10000,"*"&A1&"*")

 

[Tom Ogilvy]

Another interpretation:

Assume you are actually showing two columns - one with cities, the adjacent
column with numbers. Assume you want to know the sum of the numbers next to
the cells containing Boston

=Sumif(Sheet1!A:A,"Boston",Sheet1!B:B)

or

=Sumif(Sheet1!A:A,"*Boston*",Sheet1!B:B)

if there could be spaces in the cells as well.

regards,
Tom Ogilvy

 

[Ken Wright]

Just saw the data with the numbers in it. To do what I suggested in getting a list of uniques,
you would first have to copy the data to another area, do data / text to Columns / delimited /
space as delimiter and get rid of the numbers. Then follow the rest of my previous note.