matching part of an entry in a cell

[Andyd74]

I'm trying to return how many entries in a list that contain the first part of that entry and they meet another criteria

i.e.

A list like

Andy smith 2
Andy jones 3
Andy Simons 2

And i want to count how many Andy's there are regardless of the surname, where these entries equal "2".

Any ideas?

Thanks

Andy

[Pete_UK]

Try this, assuming your names are in column A and numbers in column B
and that these occupy rows 1 to 50:

=SUM(IF(((LEFT(A1:A50,4)="Andy")*(B1:B50=2)),1,0))

This is an array formula, so once you have typed it in (or subsequently
edit it) you must use CTRL-SHIFT-ENTER instead of just ENTER. If you do
this correctly, then Excel will wrap curly braces { } around the
formula - you must not type these yourself.

You could put the word Andy in another cell, eg C1, and then refer to
the cell in the formula.

Hope this helps.

Pete