# Creating IF function in XL97

#### criswilk

##### New Member
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.

Regards
Chris Wilkinson

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Chris

Try

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

Tony

Replies
3
Views
188
Replies
1
Views
142
Replies
4
Views
250
Replies
1
Views
140
Replies
3
Views
230

1,218,625
Messages
6,143,576
Members
450,493
Latest member
Woejeber

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back