Countif formula that counts that row if the date on one column matches the date on the other column?

Abbie G

This is my formula so far and I would like to add on to it - =COUNTIFS({Proj. Type_}, <>"OTU", {NTP}, AND(@cell >= DATE(2020, 10, 1), @cell <= DATE(2021, 9, 30))). How do I add on to this formula so that it counts it when it is 15 days and more?

Paul Newcome

Unfortunately not at the column level. Are you able to reach out to whomever can add that to the source sheet and have them add then hide it for you? The formula would be

=RWA@row - NPA@row

Then right click on the cell containing the formula and select the option at the bottom of the menu to "Convert to column formula". Then hiding the column will make it look like it never happened and shouldn't take but a minute or two to set up (in case they are worried about spending a bunch of time on it).

Then in your metrics sheet, the formula would be:

=COUNTIFS({Proj. Type_}, @cell <> "OTU", {Helper Column}, @cell >= 50)

Paul Newcome

What do you mean by "15 days and more"? Is there a duration (start date/end date), or do you mean within 15 days of one (or both) of the dates listed in your formula?

Abbie G

@Paul Newcome For example the first column is NPA and the second column is RWA. I am trying to find a formula that counts all the rows where the RWA date is 50 days or more than the NPA date. I would appreciate your help!

Paul Newcome

Ah. In that case you will need to insert a help column and calculate the difference between the two dates there. You can hide the helper column after setting it up if you want to keep the sheet looking clean. Then you can use the COUNTIFS function to count how many numbers in the helper column are greater than or equal to 50.

Abbie G

Thank you @Paul Newcome! Unfortunately, I do not have the authorization to change anything on that sheet. Is there no way to include that in the formula instead?

Paul Newcome

Unfortunately not at the column level. Are you able to reach out to whomever can add that to the source sheet and have them add then hide it for you? The formula would be

=RWA@row - NPA@row

Then right click on the cell containing the formula and select the option at the bottom of the menu to "Convert to column formula". Then hiding the column will make it look like it never happened and shouldn't take but a minute or two to set up (in case they are worried about spending a bunch of time on it).

Then in your metrics sheet, the formula would be:

=COUNTIFS({Proj. Type_}, @cell <> "OTU", {Helper Column}, @cell >= 50)

Abbie G

Thank you @Paul Newcome!!

Paul Newcome

Happy to help. 👍️