Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Return entry in column above/below cell | Excel Worksheet Functions | |||
I need help with an "If" Statement | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
enter a time into a cell, have the cell show two times the entry | Excel Worksheet Functions |