Merging CountIFS and Contains

Hi all,

Trying to get a CountIFS statement to count when a column contains the values "UE" where another column contains a certain animal type. In the example below, the value should be 2, not 0. What am I missing or am I trying to combine the wrong types of formulas?

Thank you in advance!

Current formula: =COUNTIFS([Study Number]:[Study Number], CONTAINS("*UE*", @cell ), Species:Species, ="Rat")

Find more posts tagged with

Formulas

Countif

Accepted answers

Darren Mullen

@pbartlett117 This formula would work, =COUNTIFS([Study Number]:[Study Number], CONTAINS("UE", @cell ), Species:Species, "Rat")

HOWEVER, that assumes that the cells containing "rat" don't contain any hidden characters or spaces. Which, being this is a free text field it's possible. also possible if you copy and pasted the date in from somewhere else.

So… to troubleshoot you could clear out a rat cell (sounds like a lab procedure ha ha) and retype it to see if that's the case. OR you could take the less precise route and use contains to search for rat…

=COUNTIFS([Study Number]:[Study Number], CONTAINS("UE", @cell ), Species:Species, contains("Rat",@cell))

Paul Newcome

You are also referencing entire columns with your test data in the middle of the sheet. Try applying a filter that matches the same ranges and criteria as your formula to see if maybe there is a third row somewhere that fits.

All comments

Darren Mullen

@pbartlett117

You have CONTAINS("*UE*", @cell ) with the search value as "*UE*" but you don't have any asterisk (*) in your data.

If you are thinking that * is a wildcard, it isn't. CONTAINS is searching for an exact match of what you have between the quotes.

so, it should be just Current formula: =COUNTIFS([Study Number]:[Study Number], CONTAINS("UE", @cell ), Species:Species, "Rat")

pbartlett117

@Darren Mullen

Thank you! That at least got a value, but it's counts all cells that contain UE not just the ones with Rat as the species. In the example above, I get a value of 3, not 2. I have not been able to get it to only count the rows where species is rat, do you know how to adjust the formula to do so?

Darren Mullen

@pbartlett117 This formula would work, =COUNTIFS([Study Number]:[Study Number], CONTAINS("UE", @cell ), Species:Species, "Rat")

=COUNTIFS([Study Number]:[Study Number], CONTAINS("UE", @cell ), Species:Species, contains("Rat",@cell))

Paul Newcome

pbartlett117

@Paul Newcome @Darren Mullen Thank you both! got it to work this morning. Appreciate the support!

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.