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

Find more posts tagged with

Accepted answers

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

All comments

Sameer Karkhanis

=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!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.