COUNTIFS problems


I am doing a metrics sheet to give department heads better visibility on our workflow. One of the metrics I need is how many items have been shipped, but not yet invoiced, I have a date shipped culumn (date) and an invoiced check box (boolean). Several other formulas have similar setup criteria and are also not working. the formula I have right now is 

=COUNTIFS({Open Order Report Range 4}, =ISDATE(true), {Open Order Report Range 5}, =ISBOOLEAN(false))

but it returns 0, when I look at the data there are many orders that should meet the criteria.


Another metric is for sales reps to know how many of their items have been shipped

=COUNTIFS({Open Order Report Range 6}, =Metric11, {Open Order Report Range 4}, =ISDATE(false))

Metric11 is SR name. 

also returns 0 when it should have counted items. 


  • Isaac Jose
    Isaac Jose Employee

    Hello Ryan,

    Thanks for your post!. It sounds like you want to count the number of rows that have a date in the Date Shipped column and are not checked in the Invoiced column. Try the formula below:

    • =COUNTIFS({Open Order Report Range 4}, ISDATE(@cell), {Open Order Report Range 5}, false)

    This formula looks at Range 4 and 5 then counts the rows that have dates and are not checked. There are a few adjustments that were made.

    One additional tip I have would be to edit your cross sheet reference ranges to name them intuitively. For instance, you can rename {Open Order Report Range 4} to {Open Order Report Invoice Date}. To do this, double click into the cell with your formula, click within the curly braces {} of your cross sheet reference, then click the "Edit Reference" hyperlink in the formula tooltip to open the Edit Reference window where you can edit the name of the reference in the top left corner of the window. Information about Cross Sheet References can be found here (

    I hope all of this helps!

    Kind regards,

    Isaac J

    Smartsheet Support

  • Bimmeupscotty

    I am trying the above with no luck.  The difference in my Smartsheet, is instead of a checkbox, I'm looking for a "Date Opened" to be filled and "Date Closed" is  blank to report totals (Looking for a total of all open items) and a following cell which would report if both are filled.

    This essentially would give me how many open/closed items I have depending on if the dates have been filled out.



  • Isaac Jose
    Isaac Jose Employee


    It appears that you're tracking this within a single sheet rather than across two sheets. 

    For your Items Open, try =COUNTIFS([Date Opened]:[Date Opened], ISDATE(@cell), [Date Closed]:[Date Closed], ISBLANK(@cell))

    You could also substitute ISBLANK(@cell) for NOT(ISDATE(@cell))

    For Items Closed, I think you only need to evaluate the Date Closed column as there should never be an instance where Date Closed is filled out, but Date Opened is not. I'll provide both options below:

    Only evaluate Date Closed: =COUNTIF([Date Closed]:[Date Closed], ISDATE(@cell))

    Evaluate both Date Opened and Date Closed: =COUNTIFS([Date Opened]:[Date Opened], ISDATE(@cell), [Date Closed]:[Date Closed], ISDATE(@cell)) 

    Hope this helps!

    Isaac J

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!