Adding a text in formula

Syed Muhafzal
Syed Muhafzal ✭✭✭✭✭
edited 07/02/21 in Formulas and Functions

Hi Smarties,

I have columns of checkboxes across the sheet.

I would like to make a formula of "Total Number of checked boxes are: ____"

The below formula seems to work well counting all checked boxes across the sheet.

=COUNTIFS([Value 1]4:[Value 4]106, =1) + COUNTIFS(Completed4:Completed106, =1) + COUNTIFS([Critical Tasks]4:[Critical Tasks]106, =1)

It returns 46 (correct number of checked boxes)

However, when I put in the text as below,

="Total Number of checked boxes: " + COUNTIFS([Value 1]4:[Value 4]106, =1) + COUNTIFS(Completed4:Completed106, =1) + COUNTIFS([Critical Tasks]4:[Critical Tasks]106, =1)

It returns as Total Number of Checked boxes: 8335

Looks like it is counting something else in the strings of text that I have entered.

Any ideas?

Thanks

Syed

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Syed Muhafzal

    The formula isn't counting, it's stringing your numbers together as characters. 8+3+35 or 8335. We need to let the formula know that part of the formula is text, part of the formula is a calculation.

    Try this

    ="Total Number of checked boxes: " + VALUE(COUNTIFS([Value 1]4:[Value 4]106, 1) + COUNTIFS(Completed4:Completed106, 1) + COUNTIFS([Critical Tasks]4:[Critical Tasks]106, 1))

    Does that work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Syed Muhafzal

    The formula isn't counting, it's stringing your numbers together as characters. 8+3+35 or 8335. We need to let the formula know that part of the formula is text, part of the formula is a calculation.

    Try this

    ="Total Number of checked boxes: " + VALUE(COUNTIFS([Value 1]4:[Value 4]106, 1) + COUNTIFS(Completed4:Completed106, 1) + COUNTIFS([Critical Tasks]4:[Critical Tasks]106, 1))

    Does that work for you?

    Kelly

  • Syed Muhafzal
    Syed Muhafzal ✭✭✭✭✭

    That's amazing - it works. I never knew about the Value function and it makes sense as there are two parts of the strings; text and calculation!

    Thanks @KDM

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!