Issues with "OR" for multiple columns

Can someone tell me what's wrong with my formula? I know it has to do with trying to insert an "OR" in there but I've tried multiple ways and can't get it to work. Thank you in advance!


=COUNTIFS([X Project Status]:[X Project Status], "Active", OR([Y Project Status]:[Y Project Status], "Active"), [Assigned to]:[Assigned to], "Employee Name", Type:Type, <>"Admin"))

Best Answer

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Answer ✓

    If you want the count in case either of the project statuses are Active then I doubt if you can do an OR for multiple ranges in the same COUNTIFS. However if you do have the X and Y project status columns next to each other then there is a possibility as you can define the range as [X Project Status]:[Y Project Status] and have "Active" as the criteria for both in the same COUNTIFS like,

    =COUNTIFS([X Project Status]:[Y Project Status], "Active", [Assigned to]:[Assigned to], "Employee Name", Type:Type, <>"Admin")
    

    Otherwise you may have to add two COUNTIFS,

    =COUNTIFS([X Project Status]:[X Project Status], "Active", [Assigned to]:[Assigned to], "Employee Name", Type:Type, <>"Admin") 
    + COUNTIFS([Y Project Status]:[Y Project Status], "Active", [Assigned to]:[Assigned to], "Employee Name", Type:Type, <>"Admin")
    


Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Answer ✓

    If you want the count in case either of the project statuses are Active then I doubt if you can do an OR for multiple ranges in the same COUNTIFS. However if you do have the X and Y project status columns next to each other then there is a possibility as you can define the range as [X Project Status]:[Y Project Status] and have "Active" as the criteria for both in the same COUNTIFS like,

    =COUNTIFS([X Project Status]:[Y Project Status], "Active", [Assigned to]:[Assigned to], "Employee Name", Type:Type, <>"Admin")
    

    Otherwise you may have to add two COUNTIFS,

    =COUNTIFS([X Project Status]:[X Project Status], "Active", [Assigned to]:[Assigned to], "Employee Name", Type:Type, <>"Admin") 
    + COUNTIFS([Y Project Status]:[Y Project Status], "Active", [Assigned to]:[Assigned to], "Employee Name", Type:Type, <>"Admin")
    


  • Christy Kooiman
    Christy Kooiman ✭✭✭✭

    That totally worked for my countif formulas...I need to do the same for sumif and it doesn't seem to work for that...any thoughts on how to change that?

    =SUMIFS([Hours/Week]:[Hours/Week],[X Project Status]:[X Project Status], "Active", [Assigned to]:[Assigned to], "Employee Name", Type:Type, <>"Admin") + SUMIFS([Hours/Week]:[Hours/Week],[Y Project Status]:[Y Project Status], "Active", [Assigned to]:[Assigned to], "Employee Name", Type:Type, <>"Admin")

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    Are you sure that the values in your column [Hours/Week] are numbers? If you have something like 2 hrs or 2h or 3 wks,, then the formula will not work and you will always get 0.

  • Christy Kooiman
    Christy Kooiman ✭✭✭✭

    That was my issue! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!