Count Dates on a ROW if the criteria in a corresponding COLUMN matches a specific value in another cell

[MattDaddyRamos]

Hi All,
I have two sheets that I am working with:

• Sheet 1:
  • Has the cell I want to return the result in
  • Has a Specific Identifier I need to be matched

• Sheet 2:
  • Has a Column of multiple identifiers that is used to match the specific identifier on Sheet 1
  • Has a ROW of 6 unique dates for each matching identifier

Sheet 1:
I need each cell in column I to return the number of times any date is populated in columns D-I when the loan numbers match
I need each cell in column J to return the last (highest/closest) date from the dates in columns D-I when the loan numbers match
BOTH need to ignore blanks and if there are NO dates at all return a blank cell
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Loan #[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]# of Dates[/TD]
[TD="align: center"]Last Date[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]03/05/14[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]4567[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]03/01/14[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]7890[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]03/01/14[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4321[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]7654[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]1098[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]9632[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]7412[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

















Sheet 2:
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Loan #[/TD]
[TD="align: center"]Date 1[/TD]
[TD="align: center"]Date 2[/TD]
[TD="align: center"]Date 3[/TD]
[TD="align: center"]Date 4[/TD]
[TD="align: center"]Date 5[/TD]
[TD="align: center"]Date 6[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7412[/TD]
[TD="align: center"]01/05/14[/TD]
[TD="align: center"]01/20/14[/TD]
[TD="align: center"]02/10/14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1098[/TD]
[TD="align: center"]01/03/14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7890[/TD]
[TD="align: center"]01/10/14[/TD]
[TD="align: center"]01/15/14[/TD]
[TD="align: center"]02/05/14[/TD]
[TD="align: center"]02/10/14[/TD]
[TD="align: center"]02/20/14[/TD]
[TD="align: center"]03/01/14[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7654[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4321[/TD]
[TD="align: center"]02/10/14[/TD]
[TD="align: center"]03/01/14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]01/01/14[/TD]
[TD="align: center"]01/29/14[/TD]
[TD="align: center"]02/10/14[/TD]
[TD="align: center"]02/20/14[/TD]
[TD="align: center"]02/25/14[/TD]
[TD="align: center"]03/05/14[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9632[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4567[/TD]
[TD="align: center"]01/10/14[/TD]
[TD="align: center"]02/10/14[/TD]
[TD="align: center"]02/25/14[/TD]
[TD="align: center"]03/01/14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

**For Count I've tried: =COUNTIF(INDEX('Sheet 2'!D:I,MATCH('Sheet 1'!A8,'Sheet 2'!C:C,0),0),">"&'Sheet 1'!$A$1) - where A1 is =NOW()
But when I copy the formula down to the other rows it still only counts the dates in the top row.

**For Last Date I've tried:=MAX(IF(NOT(ISERROR('Sheet 2'!D:I)),IF('Sheet 2'!C:C,'Sheet 1'!A8),'Sheet 2'!D:I))
But when I copy the formula down to the other rows it still only reads the dates in the top row.


I hope that wasn't super confusing. Any help you can offer would be greatly appreciated.

Thanks,

MattDaddyRamos

 

[Aladin Akyurek]

Try...

I2:

=COUNT(INDEX(Sheet2!$C$2:$I$9,MATCH($A2,Sheet2!$C$2:$C$9,0)))

J2:

=LOOKUP(9.99999999999999E+307,INDEX(Sheet2!$C$2:$I$9,MATCH($A2,Sheet2!$C$2:$C$9,0)))

 

[MattDaddyRamos]

Try...

I2:

=COUNT(INDEX(Sheet2!$C$2:$I$9,MATCH($A2,Sheet2!$C$2:$C$9,0)))

J2:

=LOOKUP(9.99999999999999E+307,INDEX(Sheet2!$C$2:$I$9,MATCH($A2,Sheet2!$C$2:$C$9,0)))

Thanks Aladin,

But neither seem to work properly. The one you gave me for I2 returns a blank result (should be 4) and the one for J2 returns a #REF! (should be 03/02/14).

Keep in mind that the loan lists are not going to be in the same order on both sheets and one sheet may have more loans on it than the other, if that makes a difference.

 

[Aladin Akyurek]

Thanks Aladin,

But neither seem to work properly. The one you gave me for I2 returns a blank result (should be 4) and the one for J2 returns a #REF! (should be 03/02/14).

Keep in mind that the loan lists are not going to be in the same order on both sheets and one sheet may have more loans on it than the other, if that makes a difference.

My bad. I left them incomplete...

I2:

=COUNT(INDEX(Sheet2!$C$2:$I$9,MATCH($A2,Sheet2!C2:C9,0),0))

J2:

=LOOKUP(9.99999999999999E+307,INDEX(Sheet2!$C$2:$I$9,MATCH($A2,Sheet2!$C$2:$C$9,0),0))

 

[MattDaddyRamos]

Fantastic!!!!! Works perfectly!!!! Thanks so much. Huge help.

Matt

 

[Aladin Akyurek]

Fantastic!!!!! Works perfectly!!!! Thanks so much. Huge help.

Matt

You are welcome. Thanks for providing feedback.