Countif formula that counts that row if the date on one column matches the date on the other column?
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?
Best Answer
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you @Paul Newcome!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!