counting data but not the required by date

[lefty38]

I have a formula in C1
=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$6:$B$26,ROW($B6:$B26)-ROW($B6),,1)),--(ISNA(MATCH(C6:C26,{"NR"},0))))
that counts the number of id numbers (Column B) that are not "NR" in (Column C) which works fine

In C2
the completed section counts the "completions" and does not count the NR
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C6:C26,ROW($C6:$C26)-ROW($C6),,1)),--(ISNA(MATCH(C6:C26,{"NR"},0)))) currently works fine (not counting "blank values" and "NR")

Customer wants me to include the "required by date " in the values - (Column E)
In E2
how do I NOT count the "date values" (AND "NR") values in the formula ??
=SUMPRODUCT(SUBTOTAL(3,OFFSET(E6:E26,ROW($E6:$E26)-ROW($E6),,1)),--(ISNA(MATCH(E6:E26,{"NR"},0))))

<tbody>
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="class: xl69, align: center"]C[/TD]
[TD="class: xl69, width: 43, align: center"]D[/TD]
[TD="class: xl68, width: 129, align: center"]E[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl70"]Required[/TD]
[TD="class: xl71, align: center"]8[/TD]
[TD="class: xl71, align: center"][/TD]
[TD="class: xl71, align: center"]8[/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl70"]Completed[/TD]
[TD="class: xl71, align: center"]3[/TD]
[TD="class: xl71, align: center"][/TD]
[TD="class: xl71"] (Should be) 3[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl72"]ID number[/TD]
[TD="class: xl69, align: center"]test2[/TD]
[TD="class: xl69, align: center"][/TD]
[TD="class: xl69, align: center"]test3[/TD]

[TD="class: xl67"]4[/TD]
[TD="class: xl73"]43729[/TD]
[TD="class: xl74, align: center"][/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl75, align: center"]7/22/2014[/TD]

[TD="class: xl67"]5[/TD]
[TD="class: xl73"]49773[/TD]
[TD="class: xl74, align: center"][/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl75, align: center"]12/10/2014[/TD]

[TD="class: xl67"]6[/TD]
[TD="class: xl73"]73138[/TD]
[TD="class: xl74, align: center"][/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl75, align: center"]1/17/2014[/TD]

[TD="class: xl67"]7[/TD]
[TD="class: xl73"]86293[/TD]
[TD="class: xl76, align: center"]Complete[/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl76, align: center"]Complete[/TD]

[TD="class: xl67"]8[/TD]
[TD="class: xl73"]94564[/TD]
[TD="class: xl74, align: center"][/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl75, align: center"]5/12/2014[/TD]

[TD="class: xl67"]9[/TD]
[TD="class: xl73"]103007[/TD]
[TD="class: xl75, align: center"]NR[/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl75, align: center"]NR[/TD]

[TD="class: xl67"]10[/TD]
[TD="class: xl73"]109492[/TD]
[TD="class: xl74, align: center"][/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl75, align: center"]3/4/2014[/TD]

[TD="class: xl67"]11[/TD]
[TD="class: xl73"]135375[/TD]
[TD="class: xl76, align: center"]Complete[/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl76, align: center"]Complete[/TD]

[TD="class: xl67"]12[/TD]
[TD="class: xl73"]141889[/TD]
[TD="class: xl75, align: center"]NR[/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl75, align: center"]NR[/TD]

[TD="class: xl67"]13[/TD]
[TD="class: xl73"]322737[/TD]
[TD="class: xl76, align: center"]Complete[/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl76, align: center"]Complete[/TD]

</tbody>

As always thank you for the help

 

[StephenCrump]

I may be totally missing the point here, but based on the screenshot provided, isn't:

C1: =COUNTA(B4:B13)
C2: =COUNTIF(C4:C13,"Complete")
E2: =C2

 

[lefty38]

Duh - thank you Stephen

 

[StephenCrump]

No problem. Duh moments are part and parcel of posting on the forum ... we've all been there.