COUNTIFS problems

Ryan- Impact

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

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.

• The ISDATE function checks whether the value specified within the function is a date, then returns true or false. It looks like you originally tried to define "true" within the ISDATE function where there should be a value instead. When referencing a single cell, you would enter the cell reference in the ISDATE function (EX: ISDATE([Invoice Date]1), but since you're verifying multiple values in your cell range {Open Order Report Range 4}, you'll want to use @cell to verify the values in the reference range. Information about @cell can be found here  (https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell). Information about ISDATE can be found here (https://help.smartsheet.com/function/isdate)
• Similar to ISDATE, the ISBOOLEAN function checks whether the value specified within the function is a boolean, but it isn't used to validate whether a boolean value is true or false. To check for specific boolean values in a function, you can use a 1 or a 0, or you can type "true" or "false". More information about ISBOOLEAN can be found here (https://help.smartsheet.com/function/isboolean). Information about using boolean columns in your formulas can be found in or Formulas FAQ (https://help.smartsheet.com/articles/2476091-frequently-asked-questions-about-using-formulas)

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 (https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets)

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

Hello,

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