matching part of an entry in a cell
 

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


--
Andyd74
------------------------------------------------------------------------
Andyd74's Profile: http://www.excelforum.com/member.php...o&userid=34013
View this thread: http://www.excelforum.com/showthread...hreadid=545169

matching part of an entry in a cell
 

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