Adding a text in formula
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
Best 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

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

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
Categories
Check out the Formula Handbook template!