text addition

  • Thread starter Thread starter Jay
  • Start date Start date
J

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
 
=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
 
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
 
-----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
Click to expand...
get them to work any more help would be great thank you
jay
.
Boston 1
Click to expand...
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
 
Use Harlan's solution with the wildcards (*Washington*)
or add wildcards to the cell

=COUNTIF(Range,"*"&D1&"*")
 
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&"*")
 
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
 
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.
 
Back
Top