countif??? Struggling!!! (Now with file attached)

[Johnny247]

Good morning All,
I have a problem I am looking for some help with:
I have a sheet that has a list of initials, target dates, and actual dates. (Column E,F,G)

SC 09-Mar-16 09-Mar-16
SF 09-Mar-16 09-Mar-16
SF 10-Apr-16 22-Mar-16
AW 10-Mar-16 11-Mar-16
JM 10-Mar-16 11-Mar-16
SF 10-Mar-16 11-Mar-16
JM Monitor 21-Mar-16
JK 11 Ma 16 11-Mar-16

What I want to do, is have another column that will count the amount of occasions (by month), that each set of initials goes over the target date. on sheet 2
I have tried things like =COUNTIF(Sheet1!G$5:G$700>F$5:F$700,"*Mar-16",E5) but it does not work. First attempt returns an error saying too many arguments. Next attempt returns a value of zero.

Any help on this one please??
Thanks in advance.

[Glenn Kennedy]

Countif works with one criterion. You need COUNTIFS. If you need more help, please attach an Excel sheet, not a poorly formatted list.


Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Johnny247]

Thanks for your reply. I have now attached a sample of the file. I have tried COUNTIFS, and that did not seem to work either...

[Glenn Kennedy]

Changes the dates in E4 and across to 01/03/2016 etc and format as mmmm (so you just see the month name as now).

Use this formula in E5, copied across and down:

=COUNTIFS(Sheet1!H:H,Sheet2!$A5,Sheet1!G:G,">="&Sheet2!E$4,Sheet1!G:G,"<="&EOMONTH(Sheet2!E$4,0))

If the sight of all those zeros annoys you, use this instead:

=IFERROR(1/(1/COUNTIFS(Sheet1!H:H,Sheet2!$A5,Sheet1!G:G,">="&Sheet2!E$4,Sheet1!G:G,"<="&EOMONTH(Sheet2!E$4,0))),"")

[Glenn Kennedy]

I forgot the file

[Glenn Kennedy]

I've just noticed that your thread title is pretty poor. can you go to the first post, select thread tools (near top) and amend it to soemthing that you might expect to find following a google search (e.g. COUNTIF syntax)

[Glenn Kennedy]

Did you spot my (deliberate??!!) mistake. i forgot to $ soem of the columns:

=COUNTIFS(Sheet1!$H:$H,Sheet2!$A5,Sheet1!$G:$G,">="&Sheet2!E$4,Sheet1!$G:$G,"<="&EOMONTH(Sheet2!E$4,0))

[Johnny247]

That seems to have done it! Thank you very much for the help!

[Glenn Kennedy]

Thanks for the Rep and apologies for my goof-up!!