Counting number of entries in a date column, but exclude the "Example" rows.

I have created a tracking sheet that moves everything from the prior day's entries to another sheet. However, that also deletes all of the formulas in the main sheet so I created an "example" row to maintain the formulas.

I am trying to capture the metrics on the number of "closed" customers (those with an end time) in real time but it is adding the example row that I added in the final tally.

I am currently using this formula to capture the number of "Closed" appointments:

=COUNT({I-240 Customer Check-in System - Completed}.

Is there a way to eliminate the "Example" row out of the calculations?

Best Answer

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 05/23/23 Answer ✓

    Hi @Kimberley O,

    Are your formulas in the main sheet column formulas? If not, you might try that to retain them. When rows are deleted (moved) the column formulas should remain. Hope that helps!

Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    =COUNTIF({I-240 Customer Check-in System - Completed},<>"",{Customer Name},<>"John Smith (example)

    This should work as long as the {I-240 Customer Check-in System - Completed is on the same sheet as your example customer name sheet and you do not want to count rows with the I-240 Customer Check-in System - Completed that is not blank. You will also need to create your reference to the Customer name column

  • Kimberley O
    Kimberley O ✭✭✭

    I got an #UNPARSEABLE error when I tried using this formula on the main sheet. However, I am capturing the metrics on another sheet so it would need to be a cross-sheet reference, if possible.

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    I apologize It should actually work as a crosssheet as long as you have your references set up. I was thinking one thing and typed the opposite.

    =COUNTIF({I-240 Customer Check-in System - Completed reference},<>"",{Customer Name reference},<>"John Smith (example))

  • Kimberley O
    Kimberley O ✭✭✭
    edited 05/23/23

    Still receiving an #UNPARSEABLE error for this:

    =COUNTIF({I-240 Customer Check-in System Completed}, <>"", {I-240 Customer Check-in System - Customer Name}, <> "Example John Smith"))

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 05/23/23 Answer ✓

    Hi @Kimberley O,

    Are your formulas in the main sheet column formulas? If not, you might try that to retain them. When rows are deleted (moved) the column formulas should remain. Hope that helps!

  • Kimberley O
    Kimberley O ✭✭✭

    @ker9, that worked perfectly!!! Thanks for the assistance.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!