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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    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?

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Abbie G
    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
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Abbie G
    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
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!