Getting Invalid Operation using Countifs with two fields

DKManley

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.

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?