Countif problem

[JimLau]

I have a table let say column F has dates in it and column B has employee names in it. How would I count the number of times this employee is in the column B between the two dates.

[Pete_UK]

You would use COUNTIFS function if your Excel version is 2007 or later. I see from your profile, however, that you are using XL2003, and COUNTIFS isn't available in that version - if you have updated the version and not changed your profile, please do so.

You could use a SUMPRODUCT formula instead.

Hope this helps.

Pete

[joeu2004]

If you're truly using Excel 2003 or saving as "xls", you cannot use COUNTIFS (with an "S"). So use SUMPRODUCT, to wit:

=SUMPRODUCT((B2:B1000=E2)*(F2:F1000>=D1)*(F2:F1000<=D2))

where E2 contains the employee name, D1 contains the earlier date, and D2 contains the later date.

You might want to use some form of absolute references, if you intend to copy the formula.

OTOH, if you are not actually using Excel 2003 and you do not save as "xls", you can use COUNTIFS, to wit:

=COUNTIFS(B2:B1000,E2,F2:F1000,">="&D1,F2:F1000,"<="&D2)

[JimLau]

Sorry I do use Excel 2010. Here is what I have and it won't work.

=COUNTIFS('Working Apps. in the Funnel'!F2:F227,"AL",'Working Apps. in the Funnel'!J2:J227,">="&H37,'Working Apps. in the Funnel'!F2:F200,"<="&I37)

The data is in one book and the dates are entered two different cells (a start date and end date) in another book where the result show up.

[JimLau]

Sorry I do use Excel 2010. Here is what I have and it won't work.

=COUNTIFS('Working Apps. in the Funnel'!F2:F227,"AL",'Working Apps. in the Funnel'!J2:J227,">="&H37,'Working Apps. in the Funnel'!F2:F200,"<="&I37)

The data is in one book and the dates are entered two different cells (a start date and end date) in another book where the result show up.

[Gregb11]

It looks like you need to change the end of your formula to J2:J200 instead of F2:F200. (I'm assuming your dates are in Column J)

[joeu2004]

It looks like you need to change the end of your formula to J2:J200 instead of F2:F200. (I'm assuming your dates are in Column J)

Actually, J2:J227, just like the previous terms. I assume that H37 is the start date and I37 is the end date.

[JimLau]

The formula still returns a #VALUE!

[joeu2004]

The formula still returns a #VALUE!

With Gregb's suggestion (J2:J200), yes. But with my suggestion (J2:J227), it eliminates the #VALUE error (for me). Namely:

=COUNTIFS('Working Apps. in the Funnel'!F2:F227,"AL",'Working Apps. in the Funnel'!J2:J227,">="&H37,'Working Apps. in the Funnel'!J2:J227,"<="&I37)

Of course, I cannot say whether or not COUNTIFS returns the expected value. That would depend on the values in H37, I37, F2:F227 and J2:J227, which you have not provided.

If you still have a problem using J2:J227 consistently, I suggest that you attach an Excel file that demonstrates the problem. In this forum's UI, click Go Advanced, Manage Attachments, Browse, then Upload.

[JimLau]

Thank you it worked. I didn't think the range would make a difference but it did. Will leave a Rep.