Count number of people
in [Functions]
|
From: burtlake on 29 May 2010 17:28 We will be hosting a large fund raiser. I need to know the number of people attending. Here's my problem................ Some people come as couples, and some as singles. For example, I have listed Jim & Susan Brown. But in the same list I have Bill Smith, and Ann Johnson. If ALL of these people RSVP with a yes, it represents 4 attendees. I think the key to determining if it is 1 or 2 people attending is the ampersand "&". If I have an ampersand, it should count as two; no ampersand, it is 1. How can I use a function or functions to make this distinction, anc correctly enter the number of attendees in the spredsheet?
From: Don Guillett on 29 May 2010 17:41 One way' '===== Option Explicit Sub countem() Dim c As Range Dim lr As Long Dim ms As Long lr = Cells(Rows.Count, 1).End(xlUp).Row For Each c In Range("a2:a" & lr) If InStr(c, "&") Then ms = ms + 2 Else ms = ms + 1 End If Next c MsgBox ms End Sub '======== -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "burtlake" <burtlake(a)discussions.microsoft.com> wrote in message news:DF0825BC-2175-453B-8153-F5A5865F2FD0(a)microsoft.com... > We will be hosting a large fund raiser. I need to know the number of > people > attending. Here's my problem................ > > Some people come as couples, and some as singles. For example, I have > listed Jim & Susan Brown. But in the same list I have Bill Smith, and Ann > Johnson. If ALL of these people RSVP with a yes, it represents 4 > attendees. > > I think the key to determining if it is 1 or 2 people attending is the > ampersand "&". If I have an ampersand, it should count as two; no > ampersand, > it is 1. > > How can I use a function or functions to make this distinction, anc > correctly enter the number of attendees in the spredsheet?
From: Bernard Liengme on 29 May 2010 18:01 =ISTEXT(A1)+ISNUMBER(FIND("&",A1)) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme REMINDER: These newsgroups are about to die. We will all meet again at http://social.answers.microsoft.com/Forums/en-US/category/officeexcel "burtlake" <burtlake(a)discussions.microsoft.com> wrote in message news:DF0825BC-2175-453B-8153-F5A5865F2FD0(a)microsoft.com... > We will be hosting a large fund raiser. I need to know the number of > people > attending. Here's my problem................ > > Some people come as couples, and some as singles. For example, I have > listed Jim & Susan Brown. But in the same list I have Bill Smith, and Ann > Johnson. If ALL of these people RSVP with a yes, it represents 4 > attendees. > > I think the key to determining if it is 1 or 2 people attending is the > ampersand "&". If I have an ampersand, it should count as two; no > ampersand, > it is 1. > > How can I use a function or functions to make this distinction, anc > correctly enter the number of attendees in the spredsheet?
From: burtlake on 30 May 2010 09:03 This is easy and it works. Now I need to understand why! Thanks!! "Bernard Liengme" wrote: > =ISTEXT(A1)+ISNUMBER(FIND("&",A1)) > best wishes > -- > Bernard Liengme > Microsoft Excel MVP > http://people.stfx.ca/bliengme > REMINDER: These newsgroups are about to die. We will all meet again at > http://social.answers.microsoft.com/Forums/en-US/category/officeexcel > > "burtlake" <burtlake(a)discussions.microsoft.com> wrote in message > news:DF0825BC-2175-453B-8153-F5A5865F2FD0(a)microsoft.com... > > We will be hosting a large fund raiser. I need to know the number of > > people > > attending. Here's my problem................ > > > > Some people come as couples, and some as singles. For example, I have > > listed Jim & Susan Brown. But in the same list I have Bill Smith, and Ann > > Johnson. If ALL of these people RSVP with a yes, it represents 4 > > attendees. > > > > I think the key to determining if it is 1 or 2 people attending is the > > ampersand "&". If I have an ampersand, it should count as two; no > > ampersand, > > it is 1. > > > > How can I use a function or functions to make this distinction, anc > > correctly enter the number of attendees in the spredsheet? >
From: Bernard Liengme on 31 May 2010 09:15 =ISTEXT(A1) returns TRUE if A1 has some text in it otherwise FALSE =FIND("&",A1) returns a number representing the position of & in the A1 string if there is a & and an error value if not =ISNUMBER(...) returns TRUE if FIND returns a number, otherwise FALSE So we have four possible outcomes: TRUE + TRUE TRUE + FALSE FALSE + TRUE (actually this one is unlikely!) FALSE + FALSE But when Excel see an arithmetic operator (here the addition operator +) between two Boolean values (TRUE/FALSE) it treats TRUE as 1 and FALSE as 0. So we will get 1 when there is text but no & 2 when there is text and there is a & best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme REMINDER: These newsgroups are about to die. We will all meet again at http://social.answers.microsoft.com/Forums/en-US/category/officeexcel "burtlake" <burtlake(a)discussions.microsoft.com> wrote in message news:EA9D41D4-0290-4333-9219-2CE36C4FF461(a)microsoft.com... > This is easy and it works. Now I need to understand why! Thanks!! > > "Bernard Liengme" wrote: > >> =ISTEXT(A1)+ISNUMBER(FIND("&",A1)) >> best wishes >> -- >> Bernard Liengme >> Microsoft Excel MVP >> http://people.stfx.ca/bliengme >> REMINDER: These newsgroups are about to die. We will all meet again at >> http://social.answers.microsoft.com/Forums/en-US/category/officeexcel >> >> "burtlake" <burtlake(a)discussions.microsoft.com> wrote in message >> news:DF0825BC-2175-453B-8153-F5A5865F2FD0(a)microsoft.com... >> > We will be hosting a large fund raiser. I need to know the number of >> > people >> > attending. Here's my problem................ >> > >> > Some people come as couples, and some as singles. For example, I have >> > listed Jim & Susan Brown. But in the same list I have Bill Smith, and >> > Ann >> > Johnson. If ALL of these people RSVP with a yes, it represents 4 >> > attendees. >> > >> > I think the key to determining if it is 1 or 2 people attending is the >> > ampersand "&". If I have an ampersand, it should count as two; no >> > ampersand, >> > it is 1. >> > >> > How can I use a function or functions to make this distinction, anc >> > correctly enter the number of attendees in the spredsheet? >>
|
Pages: 1 Prev: Conditional formula with text and number in cell Next: Remove non-space spaces? |