matching part of an entry in a cell

A

Andyd74

I'm trying to return how many entries in a list that contain the firs
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 th
surname, where these entries equal "2".

Any ideas?

Thanks

And
 
P

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
 
Top