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

Doyle54

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

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

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

Spectacular Response Time!!

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.