Countif Function Multiple Criteria

[Angelfish13]

Hi,

I need to find a COUNTIF (or something similar) formula. Right now I do this by hand and it takes absolutely forever. Please see below. Is there any way to achieve this with a formula? Thanks so much!

I need to create a COUNTIF function that does the following (in sheet 1 column 2):
1. Finds a "fuzzy match" between two names on two different sheets
2. If there is a match AND the last pick-up date is within the current month, it counts it

In another column, I also need it to the following (in sheet 1 column 3):
1. Finds a "fuzzy match" between two names on two different sheets
2. Finds the latest pick-up date for that name and reflects what this date is


Example Sheet 1 (where I slowly fill in column 2 and 3 by hand)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]# of pick-ups this month [/TD]
[TD]Last pick-up date [/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD]2[/TD]
[TD]11/3[/TD]
[/TR]
[TR]
[TD]Fred George[/TD]
[TD]1[/TD]
[TD]11/2[/TD]
[/TR]
[TR]
[TD]Sam Kern[/TD]
[TD]0[/TD]
[TD]10/25[/TD]
[/TR]
[TR]
[TD]Patrick Jay[/TD]
[TD]0[/TD]
[TD]10/21[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 (master data sheet - the sheet I reference to fill out sheet 1 by hand)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Pick-up date[/TD]
[/TR]
[TR]
[TD]Smith, Bob (REP)[/TD]
[TD]11/3[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD]11/2[/TD]
[/TR]
[TR]
[TD]George, Fred[/TD]
[TD]11/2[/TD]
[/TR]
[TR]
[TD]Kern, Sam (REP)[/TD]
[TD]10/25[/TD]
[/TR]
[TR]
[TD]Kern, Sam (REP)[/TD]
[TD]10/21[/TD]
[/TR]
[TR]
[TD]Jay, Patrick [/TD]
[TD]10/21[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In B2 of Sheet1 control+shift+enter, not just enter, and copy down:

=SUM((IF(ISNUMBER(SEARCH(REPLACE(A2,1,SEARCH(" ",A2),""),Sheet2!$A$2:$A$7)),Sheet2!$B$2:$B$7-DAY(Sheet2!$B$2:$B$7)+1)=TODAY()-DAY(TODAY())+1)+0)

In C2 of Sheet1 control+shift+enter, and copy down:

=MAX(IF(ISNUMBER(SEARCH(REPLACE(A2,1,SEARCH(" ",A2),""),Sheet2!$A$2:$A$7)),Sheet2!$B$2:$B$7))

Hope the fuzzy matching the above set up implements will suffice.