Issues with "OR" for multiple columns

Christy Kooiman

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"))

Sameer Karkhanis

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")

Sameer Karkhanis

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

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

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

That was my issue! Thank you!