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)

Name	# of pick-ups this month	Last pick-up date
Bob Smith	2	11/3
Fred George	1	11/2
Sam Kern	0	10/25
Patrick Jay	0	10/21

<tbody>
</tbody>

Sheet 2 (master data sheet - the sheet I reference to fill out sheet 1 by hand)

Name	Pick-up date
Smith, Bob (REP)	11/3
Bob Smith	11/2
George, Fred	11/2
Kern, Sam (REP)	10/25
Kern, Sam (REP)	10/21
Jay, Patrick	10/21

<tbody>
</tbody>

 

[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.