PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > COUNTIF "string" in cell

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

COUNTIF "string" in cell

 
 
=?Utf-8?B?Q0xS?=
Guest
Posts: n/a
 
      28th Nov 2006
Hi All.......
How might the code read to perform a check of all cells in the selected
column and count all the cells which contain a certain string at least once,
and place the total in cell B1. That is, if the string exists by itself, or
within a string of other characters in the same cell.....and if multiple
times in one cell, only count once.

If looking for JOHN,
Cell with JOHN ...would count 1
Cell with JOHN/BILL /TOM & OTHERS... would count 1
Cell with JOHN / BILL,JOHN ...would count 1
Cell with BILL, TOM ...would not count
(note the inconsistant punctuation)

TIA for any help
Vaya con Dios,
Chuck, CABGx3
 
Reply With Quote
 
 
 
 
JE McGimpsey
Guest
Posts: n/a
 
      28th Nov 2006
One way:

=COUNTIF(A:A,"*JOHN*")

In article <785D4010-A78E-4D39-933C-(E-Mail Removed)>,
CLR <(E-Mail Removed)> wrote:

> Hi All.......
> How might the code read to perform a check of all cells in the selected
> column and count all the cells which contain a certain string at least once,
> and place the total in cell B1. That is, if the string exists by itself, or
> within a string of other characters in the same cell.....and if multiple
> times in one cell, only count once.
>
> If looking for JOHN,
> Cell with JOHN ...would count 1
> Cell with JOHN/BILL /TOM & OTHERS... would count 1
> Cell with JOHN / BILL,JOHN ...would count 1
> Cell with BILL, TOM ...would not count
> (note the inconsistant punctuation)
>
> TIA for any help
> Vaya con Dios,
> Chuck, CABGx3
 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      28th Nov 2006
something like
mc=0
for each c in selection
if instr(c,"John")>0 then mc=mc+1
next c
msgbox mc
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"CLR" <(E-Mail Removed)> wrote in message
news:785D4010-A78E-4D39-933C-(E-Mail Removed)...
> Hi All.......
> How might the code read to perform a check of all cells in the selected
> column and count all the cells which contain a certain string at least
> once,
> and place the total in cell B1. That is, if the string exists by itself,
> or
> within a string of other characters in the same cell.....and if multiple
> times in one cell, only count once.
>
> If looking for JOHN,
> Cell with JOHN ...would count 1
> Cell with JOHN/BILL /TOM & OTHERS... would count 1
> Cell with JOHN / BILL,JOHN ...would count 1
> Cell with BILL, TOM ...would not count
> (note the inconsistant punctuation)
>
> TIA for any help
> Vaya con Dios,
> Chuck, CABGx3
>
>
 
Reply With Quote
 
=?Utf-8?B?Q0xS?=
Guest
Posts: n/a
 
      28th Nov 2006
Cool, thanks JE..........just tweaked it a bit for my application...

Sub FindName()
Range("B1").Formula = "=COUNTIF(n:n,""*john*"")"
End Sub

Many thanks..........
Vaya con Dios,
Chuck, CABGx3


"JE McGimpsey" wrote:

> One way:
>
> =COUNTIF(A:A,"*JOHN*")
>
> In article <785D4010-A78E-4D39-933C-(E-Mail Removed)>,
> CLR <(E-Mail Removed)> wrote:
>
> > Hi All.......
> > How might the code read to perform a check of all cells in the selected
> > column and count all the cells which contain a certain string at least once,
> > and place the total in cell B1. That is, if the string exists by itself, or
> > within a string of other characters in the same cell.....and if multiple
> > times in one cell, only count once.
> >
> > If looking for JOHN,
> > Cell with JOHN ...would count 1
> > Cell with JOHN/BILL /TOM & OTHERS... would count 1
> > Cell with JOHN / BILL,JOHN ...would count 1
> > Cell with BILL, TOM ...would not count
> > (note the inconsistant punctuation)
> >
> > TIA for any help
> > Vaya con Dios,
> > Chuck, CABGx3
>
 
Reply With Quote
 
=?Utf-8?B?Q0xS?=
Guest
Posts: n/a
 
      28th Nov 2006
Thanks Don..........works good but seems to be "case sensitive".......can
anything be done to change that?

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

> something like
> mc=0
> for each c in selection
> if instr(c,"John")>0 then mc=mc+1
> next c
> msgbox mc
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "CLR" <(E-Mail Removed)> wrote in message
> news:785D4010-A78E-4D39-933C-(E-Mail Removed)...
> > Hi All.......
> > How might the code read to perform a check of all cells in the selected
> > column and count all the cells which contain a certain string at least
> > once,
> > and place the total in cell B1. That is, if the string exists by itself,
> > or
> > within a string of other characters in the same cell.....and if multiple
> > times in one cell, only count once.
> >
> > If looking for JOHN,
> > Cell with JOHN ...would count 1
> > Cell with JOHN/BILL /TOM & OTHERS... would count 1
> > Cell with JOHN / BILL,JOHN ...would count 1
> > Cell with BILL, TOM ...would not count
> > (note the inconsistant punctuation)
> >
> > TIA for any help
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
>
>
>
 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      28th Nov 2006
Sub countjohn()
For Each c In Selection
If InStr(UCase(c), "JOHN") > 0 Then mc = mc + 1
Next c
MsgBox mc
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"CLR" <(E-Mail Removed)> wrote in message
news:CF249236-9247-4E1D-A88F-(E-Mail Removed)...
> Thanks Don..........works good but seems to be "case sensitive".......can
> anything be done to change that?
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Don Guillett" wrote:
>
>> something like
>> mc=0
>> for each c in selection
>> if instr(c,"John")>0 then mc=mc+1
>> next c
>> msgbox mc
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "CLR" <(E-Mail Removed)> wrote in message
>> news:785D4010-A78E-4D39-933C-(E-Mail Removed)...
>> > Hi All.......
>> > How might the code read to perform a check of all cells in the selected
>> > column and count all the cells which contain a certain string at least
>> > once,
>> > and place the total in cell B1. That is, if the string exists by
>> > itself,
>> > or
>> > within a string of other characters in the same cell.....and if
>> > multiple
>> > times in one cell, only count once.
>> >
>> > If looking for JOHN,
>> > Cell with JOHN ...would count 1
>> > Cell with JOHN/BILL /TOM & OTHERS... would count 1
>> > Cell with JOHN / BILL,JOHN ...would count 1
>> > Cell with BILL, TOM ...would not count
>> > (note the inconsistant punctuation)
>> >
>> > TIA for any help
>> > Vaya con Dios,
>> > Chuck, CABGx3
>> >
>> >
>>
>>
>>
 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      28th Nov 2006
Or if you no longer need the formula

'Range("B1").Formula = "=COUNTIF(n:n,""*john*"")"
Range("b1").Value = Application.CountIf(Columns("n"), "*john*")

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"CLR" <(E-Mail Removed)> wrote in message
news:2F525567-459D-465E-A9A9-(E-Mail Removed)...
> Cool, thanks JE..........just tweaked it a bit for my application...
>
> Sub FindName()
> Range("B1").Formula = "=COUNTIF(n:n,""*john*"")"
> End Sub
>
> Many thanks..........
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "JE McGimpsey" wrote:
>
>> One way:
>>
>> =COUNTIF(A:A,"*JOHN*")
>>
>> In article <785D4010-A78E-4D39-933C-(E-Mail Removed)>,
>> CLR <(E-Mail Removed)> wrote:
>>
>> > Hi All.......
>> > How might the code read to perform a check of all cells in the selected
>> > column and count all the cells which contain a certain string at least
>> > once,
>> > and place the total in cell B1. That is, if the string exists by
>> > itself, or
>> > within a string of other characters in the same cell.....and if
>> > multiple
>> > times in one cell, only count once.
>> >
>> > If looking for JOHN,
>> > Cell with JOHN ...would count 1
>> > Cell with JOHN/BILL /TOM & OTHERS... would count 1
>> > Cell with JOHN / BILL,JOHN ...would count 1
>> > Cell with BILL, TOM ...would not count
>> > (note the inconsistant punctuation)
>> >
>> > TIA for any help
>> > Vaya con Dios,
>> > Chuck, CABGx3
>>
 
Reply With Quote
 
=?Utf-8?B?Q0xS?=
Guest
Posts: n/a
 
      28th Nov 2006
Thanks Don, this new one requires the entire string to be UpperCase. Can it
be modified to be not case sensitive to any of the characters in the string?

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

> Sub countjohn()
> For Each c In Selection
> If InStr(UCase(c), "JOHN") > 0 Then mc = mc + 1
> Next c
> MsgBox mc
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "CLR" <(E-Mail Removed)> wrote in message
> news:CF249236-9247-4E1D-A88F-(E-Mail Removed)...
> > Thanks Don..........works good but seems to be "case sensitive".......can
> > anything be done to change that?
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Don Guillett" wrote:
> >
> >> something like
> >> mc=0
> >> for each c in selection
> >> if instr(c,"John")>0 then mc=mc+1
> >> next c
> >> msgbox mc
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "CLR" <(E-Mail Removed)> wrote in message
> >> news:785D4010-A78E-4D39-933C-(E-Mail Removed)...
> >> > Hi All.......
> >> > How might the code read to perform a check of all cells in the selected
> >> > column and count all the cells which contain a certain string at least
> >> > once,
> >> > and place the total in cell B1. That is, if the string exists by
> >> > itself,
> >> > or
> >> > within a string of other characters in the same cell.....and if
> >> > multiple
> >> > times in one cell, only count once.
> >> >
> >> > If looking for JOHN,
> >> > Cell with JOHN ...would count 1
> >> > Cell with JOHN/BILL /TOM & OTHERS... would count 1
> >> > Cell with JOHN / BILL,JOHN ...would count 1
> >> > Cell with BILL, TOM ...would not count
> >> > (note the inconsistant punctuation)
> >> >
> >> > TIA for any help
> >> > Vaya con Dios,
> >> > Chuck, CABGx3
> >> >
> >> >
> >>
> >>
> >>
>
>
>
 
Reply With Quote
 
=?Utf-8?B?Q0xS?=
Guest
Posts: n/a
 
      28th Nov 2006
How slick it is...........thanks Don!

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

> Or if you no longer need the formula
>
> 'Range("B1").Formula = "=COUNTIF(n:n,""*john*"")"
> Range("b1").Value = Application.CountIf(Columns("n"), "*john*")
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "CLR" <(E-Mail Removed)> wrote in message
> news:2F525567-459D-465E-A9A9-(E-Mail Removed)...
> > Cool, thanks JE..........just tweaked it a bit for my application...
> >
> > Sub FindName()
> > Range("B1").Formula = "=COUNTIF(n:n,""*john*"")"
> > End Sub
> >
> > Many thanks..........
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "JE McGimpsey" wrote:
> >
> >> One way:
> >>
> >> =COUNTIF(A:A,"*JOHN*")
> >>
> >> In article <785D4010-A78E-4D39-933C-(E-Mail Removed)>,
> >> CLR <(E-Mail Removed)> wrote:
> >>
> >> > Hi All.......
> >> > How might the code read to perform a check of all cells in the selected
> >> > column and count all the cells which contain a certain string at least
> >> > once,
> >> > and place the total in cell B1. That is, if the string exists by
> >> > itself, or
> >> > within a string of other characters in the same cell.....and if
> >> > multiple
> >> > times in one cell, only count once.
> >> >
> >> > If looking for JOHN,
> >> > Cell with JOHN ...would count 1
> >> > Cell with JOHN/BILL /TOM & OTHERS... would count 1
> >> > Cell with JOHN / BILL,JOHN ...would count 1
> >> > Cell with BILL, TOM ...would not count
> >> > (note the inconsistant punctuation)
> >> >
> >> > TIA for any help
> >> > Vaya con Dios,
> >> > Chuck, CABGx3
> >>
>
>
>
 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      28th Nov 2006
???

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"CLR" <(E-Mail Removed)> wrote in message
news:6347914C-B792-4681-B21B-(E-Mail Removed)...
> Thanks Don, this new one requires the entire string to be UpperCase. Can
> it
> be modified to be not case sensitive to any of the characters in the
> string?
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Don Guillett" wrote:
>
>> Sub countjohn()
>> For Each c In Selection
>> If InStr(UCase(c), "JOHN") > 0 Then mc = mc + 1
>> Next c
>> MsgBox mc
>> End Sub
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "CLR" <(E-Mail Removed)> wrote in message
>> news:CF249236-9247-4E1D-A88F-(E-Mail Removed)...
>> > Thanks Don..........works good but seems to be "case
>> > sensitive".......can
>> > anything be done to change that?
>> >
>> > Vaya con Dios,
>> > Chuck, CABGx3
>> >
>> >
>> >
>> > "Don Guillett" wrote:
>> >
>> >> something like
>> >> mc=0
>> >> for each c in selection
>> >> if instr(c,"John")>0 then mc=mc+1
>> >> next c
>> >> msgbox mc
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "CLR" <(E-Mail Removed)> wrote in message
>> >> news:785D4010-A78E-4D39-933C-(E-Mail Removed)...
>> >> > Hi All.......
>> >> > How might the code read to perform a check of all cells in the
>> >> > selected
>> >> > column and count all the cells which contain a certain string at
>> >> > least
>> >> > once,
>> >> > and place the total in cell B1. That is, if the string exists by
>> >> > itself,
>> >> > or
>> >> > within a string of other characters in the same cell.....and if
>> >> > multiple
>> >> > times in one cell, only count once.
>> >> >
>> >> > If looking for JOHN,
>> >> > Cell with JOHN ...would count 1
>> >> > Cell with JOHN/BILL /TOM & OTHERS... would count 1
>> >> > Cell with JOHN / BILL,JOHN ...would count 1
>> >> > Cell with BILL, TOM ...would not count
>> >> > (note the inconsistant punctuation)
>> >> >
>> >> > TIA for any help
>> >> > Vaya con Dios,
>> >> > Chuck, CABGx3
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Running macros in newly created sheets | Automatic Paste to different workbooks »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find >cell_value with COUNTIF. Can't Refer to Cell Value in "quotemarks" bony_tony Microsoft Excel Worksheet Functions 1 5th Jul 2010 07:04 AM
VBA Syntax for Countif condition where only 1st char in Range of each cell = "M" EagleOne@discussions.microsoft.com Microsoft Excel Programming 5 7th Oct 2009 07:19 PM
=COUNTIF(A5:A51,"=D240:Q251") Why will the cell D220 not TOTAL Terry Microsoft Excel Programming 3 24th Nov 2007 08:50 PM
What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell? mar_male@wp.pl Microsoft Excel Programming 9 14th Nov 2005 09:14 PM
What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell? mar_male@wp.pl Microsoft Excel Worksheet Functions 3 14th Nov 2005 01:48 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 12:03 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top