Formula to count rows where multiple criteria are met

[tbrookes]

Good Morning


I have a large spreadsheet with a series of actions which includes the date the action was added, the date the action was scheduled to occur and the date the action was completed.


What I want to do is create a formula that will count the number of instances (rows) where the completed date is greater than the scheduled date and the added date equals criteria that I specify.


Can anyone assist?


Thanks in advance.

 

[The_devas]

Hi tbrookes, there are a couple of different ways to go about this depending on how you have your worksheet set up. Also, how the formula is written would depend on what you mean by "and the added date equals criteria I specify". What criteria do you want it to meet and where would you specify it? To simply count the number of instances where the completed date is greater than the scheduled date, I can think of two quick ways just off the top of my head.
[table="width: 500, class: grid"]
[tr]
[td]Action[/td]
[td]date added[/td]
[td]date scheduled[/td]
[td]date completed[/td]
[td]Action Over Schedule?[/td]
[td]Total Actions Over Schedule Version 1[/td]
[td]Total Actions Over Schedule Version 2[/td]
[/tr]
[tr]
[td]Walk Dog[/td]
[td]1/1/2016[/td]
[td]1/10/2016[/td]
[td]1/13/2016[/td]
[td]=IF(COUNTIF(D2,">"&C2)=1,"yes","no")[/td]
[td]=COUNTIF(E2:E4,"=yes")[/td]
[td]=COUNTIF(D2,">"&C2)+COUNTIF(D3,">"&C3)+COUNTIF(D4,">"&C4)[/td]
[/tr]
[tr]
[td]Take Trash[/td]
[td]1/1/2016[/td]
[td]1/13/2016[/td]
[td]1/13/2016[/td]
[td]=IF(COUNTIF(D3,">"&C3)=1,"yes","no")[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Sweep[/td]
[td]1/3/2016[/td]
[td]1/12/2016[/td]
[td]1/10/2016[/td]
[td]=IF(COUNTIF(D4,">"&C4)=1,"yes","no")[/td]
[td][/td]
[td][/td]
[/tr]
[/table]

As shown, there are two versions. The first, uses a COUNTIF function for each row then another to count the total number that were over. This is easy to do because you can just flashfill the COUNTIFs for each row. Unfortunately, that means you have another column in your worksheet that you may or may not want. The second version stacks COUNTIFs in a single cell. This takes up less space in your workbook but very quickly becomes tedious and tiresome. Plus, these formulas will need to be altered to include whatever that other criteria is that you are wanting to specify.
Again, I understand this isn't fully what you are asking, but maybe we can work from here as I better understand what you are looking for.

 

[tbrookes]

Thanks for such a quick response.

The first tab of my workbook has all the data - I also have a column that displays the 'added month' and another one for the 'added year' - i.e. =TEXT(S169,"MMMM")
On the second tab I have a table set up that lists each month and year across the top and on the left hand side a heading which says "Actions completed after scheduled date"
What I would like to do is for each cell - e.g. the cell under the heading "July 2015" count the number of rows where the completed date is after the scheduled date and the Added Date is in July 2015

Hope that makes sense!
Regards

 

[The_devas]

Ok, I think I get what you are saying. Is there any way you could either PM me and send a small example of your workbook, or take some screenshots? That way I can create the formula that will work with what you have.

 

[Aladin Akyurek]

Thanks for such a quick response.

The first tab of my workbook has all the data - I also have a column that displays the 'added month' and another one for the 'added year' - i.e. =TEXT(S169,"MMMM")
On the second tab I have a table set up that lists each month and year across the top and on the left hand side a heading which says "Actions completed after scheduled date"
What I would like to do is for each cell - e.g. the cell under the heading "July 2015" count the number of rows where the completed date is after the scheduled date and the Added Date is in July 2015

Hope that makes sense!
Regards

Care to post a scaled-down sample from your second tab: Such a sample can be posted here using one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl.