Count If one column meets two criteria

I am trying to adjust some formulas we have for budgeting replacement computers. The sheet references another sheet and previously was just counting based on a date range to count computers due for replacement. I now wanted to expand this out further and use it to count Windows replacements and Mac replacements. I need the formula to count from a dropdown if the OS field meets "Windows 11 Professional" OR "Windows 11 Enterprise" from the source sheet. I am finding that it will count one or the other just fine but struggling to have it count if it is matching one or the other. The formula I am trying is:

=COUNTIFS({MEDC Active Computers Rec'd Date}, >=DATE(2021, 9, 1), {MEDC Active Computers Rec'd Date}, <=DATE(2021, 9, 31), {MEDC Active Computers OS}, "Windows 11 Enterprise", {MEDC Active Computers OS}, "Windows 11 Professional")

I think I need to make the last two statements an OR in some way to have it count correctly.

Find more posts tagged with

Formulas

Accepted answers

All comments

Adam Murphy

List the range once in the countifs formula and make the condition something like OR(CONTAINS("Windows 11 Enterprise", @cell), CONTAINS("Windows 11 Professional", @cell)) . Hope this helps!

degarmod

How can I reference my external sheet this way? Or do I not need to do it? I tried it with and without and neither worked. The sheet I am referencing is: MEDC Active Computers and the Cell reference is titled: MEDC Active Computers OS

degarmod

@Adam Murphy I was playing around a little more and have this set up now, but I am getting #UNPARSEABLE.

=COUNTIFS({MEDC Active Computers Rec'd Date}, >=DATE(2020, 10, 1), {MEDC Active Computers Rec'd Date}, <=DATE(2020, 10, 31), AND(CONTAINS("Windows 11 Enterprise", {MEDC Active Computers OS}@cell), CONTAINS("Windows 11 Professional", {MEDC Active Computers OS}@cell)))

Adam Murphy

The cross-sheet references are listed with {} while in-sheet is []. You have to create those cross-sheet references manually.

Ok you changed it to an AND statement meaning you want both to be true (enterprise and professional). It should be an OR statement.

I suspect the UNPARSEABLE is because you have not created those cross-sheet references, is that the case?

degarmod

I ended up accomplishing this an alternative way, an additional hidden column with a checkbox running on an automation.

I need it to count the number of times per month enterprise or professional show up so it will count correctly. I think I have the cross sheet references added a shown in my reply at 11am but it still wouldn't render properly. Even from within the formula creating the cross sheet references it was still not showing a count and just returning UNPARSEABLE or INCORRECTARGUMENT

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