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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.