A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Count number of people



 
 
Thread Tools Display Modes
  #1  
Old May 29th, 2010, 10:28 PM posted to microsoft.public.excel.worksheet.functions
burtlake
external usenet poster
 
Posts: 15
Default Count number of people

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?
  #3  
Old May 29th, 2010, 11:01 PM posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
external usenet poster
 
Posts: 516
Default Count number of people

=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/...ry/officeexcel

"burtlake" wrote in message
news
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?


  #4  
Old May 30th, 2010, 02:03 PM posted to microsoft.public.excel.worksheet.functions
burtlake
external usenet poster
 
Posts: 15
Default Count number of people

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/...ry/officeexcel

"burtlake" wrote in message
news
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?


  #5  
Old May 31st, 2010, 02:15 PM posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
external usenet poster
 
Posts: 516
Default Count number of people

=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/...ry/officeexcel


"burtlake" wrote in message
...
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/...ry/officeexcel

"burtlake" wrote in message
news
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?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.