Can you help with Countif and Date? I am actually trying to do Countifs calculations

in which I am looking for the number of instances of say Joe's completed records between two dates. but I cannot get Countif to Accurately calculate Countif when the date in the calculation is stored in a date cell. If I use the actual date in my formula calculation, everything is fine. But I want the user to be able to change the date without modifying the cell directly. I want to countif to be dynamic. Here's what happens if I do the calculation with the dates separately and coming from cells:

=COUNTIF(PadInsp:PadInsp, ">"11/1/2019") Result - 15 Where start date is 11/1/2019 and PadInsp goes from 10/1/2019 to 2/14/2020. There are 15 Actual instances

=COUNTIF(PadInsp:PadInsp, ">StartDate") Result - 0 Where StartDate is 11/1/2019 and PadInsp goes from 10/1/2019 to 2/14/2020. There are 15 Actual instances

I tried a working Excel formula which is

=COUNTIF(PadInsp:PadInsp, ">"&StartDate) Result in SS - Imparcible

Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    smartsheet doesn't require quotes the same way excel does. For smartsheet only use quotes on text values you specifically are looking for, not the operators.

    =countif(padinsp:padinsp, date(2019,11,1))

    =countif(padinsp:padinsp,> startdate@row)

  • Doyle54
    Doyle54 ✭✭✭✭✭

    Smart response and Thankyou. In my sumsheet setup I am trying to slip in a few fields to help my officers target certain information they need. Scenario - the supervisor wants to check how many oil pads his field officers have checked in the previous quarter. So, they open the Summary Report and change the dates to the quarter they are interested and my assumption is that SS Sum Report will update per the changed startdate and enddate. I'd welcome a form for the report but since forms are not for reports and reports update data, I thought that was the best approach.

    Does that work?

  • Doyle54
    Doyle54 ✭✭✭✭✭

    Spectacular Response Time!!

  • L_123
    L_123 ✭✭✭✭✭✭

    It works if they have licenses. In order to change report fields you need to have a license. An option is to use a form with a helper column on the main sheet. You can set the criteria for the helper column to post a 1 (or checkbox) if the criteria from the other sheet with the webform is met. Then on the form use the option to go to a dashboard or report after submission to take them to the requested data. (sometimes smartsheet doesn't calculate fast enough and a refresh is needed, but with moderate-low levels of data and formulas it shouldn't happen to often.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!