Count of text in a column based off a date range in another column

[hofst038]

Really needs some help on this: I am trying to get a count of something that is in a given date range.

Date	Lead Tech	Non Lead Tech	Completed
3/11/2016			Y
3/15/2016			N
3/16/2016			N
3/17/2016			Y
3/20/2016			N

<tbody>
</tbody>

So I want a count of all the “Y” values in the Completed column based off the date range of 3/11/2016-3/18/2016. Can someone please help me out?

 

[Special-K99]

Try

=COUNTIFS(A1:A5,">="&DATEVALUE("3/11/2016),A1:A5,"<="&DATEVALUE("3/18/2016),D1:D5,"Y")

 

[Raga]

See if this helps

=COUNTIFS($A$1:$A$6,">=" &A2, $A$1:$A$6, "<="&A3, $D$1:$D$6, "Y")

'change the A2 and A3 to the desired ranges or the values'


Raga

 

[hofst038]

I tried that and it looks like there are too few arguments when inserting this function. Thanks for trying it out.

 

[Joe4]

Which version of Excel are you running?

Special-K99's formula is just missing a few double-quotes after the dates:

=COUNTIFS(A1:A5,">="&DATEVALUE("3/11/2016"),A1:A5,"<="&DATEVALUE("3/18/2016"),D1:D5,"Y")

 

[hofst038]

I got it, thanks guys. Final product was: =COUNTIFS($A$1:$A$5, ">=" &DATEVALUE("3/11/2016"),$A$1:$A$5, "<=" &DATEVALUE("3/18/2016"),$D$1:$D$5, "Y")

 

[Joe4]

I got it, thanks guys. Final product was: =COUNTIFS($A$1:$A$5, ">=" &DATEVALUE("3/11/2016"),$A$1:$A$5, "<=" &DATEVALUE("3/18/2016"),$D$1:$D$5, "Y")

I guess you missed my last post!

 

[hofst038]

Ha thanks Joe4, I didnt get it until after. I did; however, run into a problem.....The function seems to be ignoring the two dates inside it and it picking up every date. Anyone know how to correct this?

 

[Joe4]

It doesn't do that for me. It is working as it should for me (and returning 2).
Are you sure you have your ranges correct?

Are you using a European or American version of Excel?

Are you also sure that your dates are actually entered as dates and not text?
An easy well to tell is to try to apply the following formula to one of the date:

=ISNUMBER(A1)

If it returns FALSE, the entry is not numeric/date, but rather text.

 

[hofst038]

I forgot to mention, the dates it is looking up is pulled in via V lookup from another file... could this possibly be part of the problem?