Count number of times rows in one column match rows in a second column between 2 dates.

[DukeJCDC]

I've been searching for this for a couple days now and have come across anything. I know it can be done in VBA, but I'd like to know if it can be done with a formula.

I've got 2 columns I need to count how many times they match between dates listed in a 3rd column.

Example:
On Sheet one there is....
Date Orders
1/1/14 1001
1/1/14 1002
1/3/14 1003
2/4/14 1004

On sheet two there is....
Job
1001
1001
1001
1002
1002
1003
1004
1004
1004
1004


And lets say I am wanting a formula that counts the amount of times the order number matches the job number for the month of February.

 

[ghrain22]

I think you're looking for countifs function:

http://office.microsoft.com/en-us/excel-help/countifs-function-HA010047494.aspx

http://office.microsoft.com/en-us/excel-help/countif-HP005209029.aspx

 

[DukeJCDC]

The problem is, my criteria would be 'Sheet1'!B:B='Sheet2'!A:A and just countif doesn't allow you to count comparing entire columns as criteria.

 

[ghrain22]

Sure you can:
=COUNTIF(Sheet2!A:A,"="&Sheet1!A1)
copy that down your sheet

 

[Aladin Akyurek]

E2: 1-Feb-14

F2:

=SUMPRODUCT(
  --(Sheet1!$A$2:$A$100>=E2),
  --(Sheet1!$A$2:$A$100<=EOMONTH(E2,0)),
  --ISNUMBER(MATCH(Sheet1!$B$2:$B$100,Sheet2!$A$2:$A$100,0)))

 

[DukeJCDC]

The problem is, my criteria would be 'Sheet1'!B:B='Sheet2'!A:A and just countif doesn't allow you to count comparing entire columns as criteria.

The spreadsheet I'm working with, Sheet1 has 25k rows of data and sheet2 has 46k rows. And they both get longer daily. I'm wanting an equation for the back end of everything instead of the front end.

 

[Aladin Akyurek]

The spreadsheet I'm working with, Sheet1 has 25k rows of data and sheet2 has 46k rows. And they both get longer daily. I'm wanting an equation for the back end of everything instead of the front end.

Since you seem to reply to yourself, post #5 must be of no use to you.

 

[DukeJCDC]

Since you seem to reply to yourself, post #5 must be of no use to you.

I've also been running circles at work and haven't had a chance to try what you posted till just now. Looks like what you have would work, but I'm on a laptop atm and it can't handle all those calculations. Thanks for your help though! I think I'll have to figure out a macro.

 

[Aladin Akyurek]

I've also been running circles at work and haven't had a chance to try what you posted till just now. Looks like what you have would work, but I'm on a laptop atm and it can't handle all those calculations. Thanks for your help though! I think I'll have to figure out a macro.

How did you implement the suggestion? That's, how does the implemented formula look like?

 

[DukeJCDC]

How did you implement the suggestion? That's, how does the implemented formula look like?

I'm not able to use a formula it's not looking like. Too much data to process, the calculation locks up excel. I'm going to be working on a macro this weekend that will hopefully be able to put together a collection and count it out that way.