Need to count the number of FirstNames in a column

[jimdandy745]

I have a spreadsheet that I would like to get a "headcount" for our membership. Example:

A B C D
1 LastName FirstName email address
2 Doe Jon and Mary jondoe@yahoo.com 2
3 Doe Harry harry@gmail.com 1
4 Jones Jim and Judy jandj@Hotmail.com 2
5 Smith Elizabeth Elizabeth@outlook.com 1
6 Smith Ralph ralph@msn.com 1

This is my thought.
A formula that IF cells B2:B6 contains the word "and" then that cell = a count of 2, else count = 1
"Total Headcount" based on above example should report 7.

OR I could create a column (D) with formula to do:
IF B(x) contains the word "and" then count=2, else count = 1 and then
total

 

[AhoyNC]

Maybe:

This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	LastName	FirstName	email address		Members
2	Doe	Jon and Mary	jondoe@yahoo.com		7
3	Doe	Harry	harry@gmail.com
4	Jones	Jim and Judy	jandj@Hotmail.com
5	Smith	Elizabeth	Elizabeth@outlook.com
6	Smith	Ralph	ralph@msn.com
Sheet2

 

[Rick Rothstein]

Here is a normally-entered formula that should also work...

=COUNTA(B2:B6)+COUNTIF(B2:B6,"*and*")

 

[jimdandy745]

Here is a normally-entered formula that should also work...

=COUNTA(B2:B6)+COUNTIF(B2:B6,"*and*")

Yep, That works great. Thanks so much. There are over 800 members in our computer club and this makes it A LOT easier to get accurate headcount