Getting Invalid Operation using Countifs with two fields

I am trying to get a count of the number of rows that have a status of Task Assist, and who have an overall percent complete value of 100%. This is the formula I thought I needed to get that to work, but it is throwing back an invalid operation error. Does anyone have any idea how I could resolve?

=COUNTIFS(Status:Status = "Tech Assist", [Overall Percent Complete]:[Overall Percent Complete] = "100%")

I appreciate any help I could get.

Find more posts tagged with

Accepted answers

All comments

Kerry St. Thomas

The syntax is subtly off. The format is =COUNTIFS(Range1, Criterion1, Range2, Criterion2, ….) So in your formula, replace the = (equals) signs inside the parentheses with , (comma). There is also another thing to consider - is "100%" is a number, even if formatted as a percent. So when you fix the formatting error, the quotation marks might result in the formula returning 0 (depending on how you've set up your sheet). So that's one other thing you may want to investigate while troubleshooting.

More info about COUNTIFS:
https://help.smartsheet.com/function/countifs

Good luck!

DKManley

UPDATE: I figured it out. I am embarrassed to say it did not occur to me initially to change the 100 to a 1 instead (because without the percentage, that is the full value). Thanks for the help!

I have been trying to manipulate the formula based on your info above but am still having issues. Here is where I left it - it does give me a number (rather than an error) but I have two test rows set up to meet this criteria, yet the formula returns zero as the count

=COUNTIFS(Status:Status, "Tech Assist", [Overall Percent Complete]:[Overall Percent Complete], 100)

It is definitely the overall percent complete part that is tripping it up - if I do just the first part with a COUNTIF it works just fine - I have tried with an equals instead of a comma, with and without a percentage sign, and with and without quotes, all throwing some kind of error. Any other thoughts?

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.