Creating IF function in XL97

[criswilk]

Hi All,

What I would like to be able to do is create a basic IF function that will test cell A1 and look for '&' and then in cell B2 return the result of how many people are in the cell.

A1 has 2 people in that cell so it would return a value of 2 & then cell A2 would return a value of 1 & then I can use a sum formula to calculate the total of column B.

Example

A B
1 L&P Citizen 2
2 Z Peter 1
3 J&M Doe 2
5

I'm using Excel 97 & Windows XP Pro SP2.

Thanks in advanve for your help.

Regards
Chris Wilkinson

 

[acw]

Chris

Try

=LEN(A1) - LEN(SUBSTITUTE(A1,"&",""))+1

Tony