How to use Countifs for rows that start with a certain character?

I am trying to use countifs formula. I want it to return how many there are that meet two criteria- the first being the first digit of a three digit number in one column is 1--. It would also have to be "Completed" in another column.

=COUNTIFS({Number}, HAS(LEFT(@cell, 1)= "1:"), {Status}, "Completed")

Find more posts tagged with

Formulas

Accepted answers

All comments

Mike TV

@RGarrels

I couldn't get this working using HAS but I was able to get it working using CONTAINS:

=COUNTIFS(Number:Number, CONTAINS("1", LEFT(@cell, 1)), Status:Status, "Completed")

I'm not sure what the colon is about in your formula, "1:", so maybe you need to use this instead:

=COUNTIFS(Number:Number, CONTAINS("1:", LEFT(@cell, 1)), Status:Status, "Completed")

Mike TV

Oh ya, you'll need to substitute Number:Number and Status:Status with your cross-sheet references.

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.