Adding a text in formula

Syed Muhafzal

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

Kelly Moore

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

Kelly Moore

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

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