Count number of people

[burtlake]

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?

 

[Don Guillett]

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
'========

 

[Bernard Liengme]

=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]

This is easy and it works. Now I need to understand why! Thanks!!

 

[Bernard Liengme]

=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