Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

COUNTIFS and with 2 different values in same column?

edited 12/09/19 in Archived 2017 Posts

Hi there,

I'm trying to create a formula to count when 2 different types of activity in a column ("Key Initiative" and "Initiative" have the value "Complete".

I've been trying different ways to organize COUNTIFS to solve the problem, but I'm getting either "0" or #Unparse as a result.

The most straightforward formula I've used is 

=COUNTIFS(Type8:Type40, "Key Initiative", Type8:Type40, "Initiative", Status8:Status40, "Complete"

I get "0" as the answer.

Can anyone help me solve this?




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    COUNTIFS is a series of AND statements. You need an OR kind of thing:

    This should work:

    =COUNTIFS(Type8:Type40, "Key Initiative", Status8:Status40, "Complete") + =COUNTIFS(Type8:Type40, "Initiative", Status8:Status40, "Complete")


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    I always forget that simple trick! I need to remember the Countifs can be added together. frown Thanks for reminding me! smiley I always try to come at these types of requests with a complicated formula. Sometimes simple math is the best solution! 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I try to do the simple solutions first, and only get complicated if those don't work. I'm lazy in that way.

    I try to keep in mind what the functions return value type is, which helps with the simple solutions.


  • Rafsun Royal
    Rafsun Royal ✭✭✭✭

     Craig, I am using this but shwing "Invalid Operation:


    =COUNTIF([Current Overall Status]:[Current Overall Status], "QA") + =COUNTIF([Current Overall Status]:[Current Overall Status], "UAT")


    I am trying to count the total tasks that are in either QA or UAT.




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Get rid of the second = sign


This discussion has been closed.