Countifs with multiple crtiteria

Hello,

I have been going at this for a while and not able to see what I may be doing wrong.

I am trying to count all EDAP Data Support issues which was created after 01/01/2023, which are still open ( not in Done or cancelled status)

Here is the formula Im using .

There is something wrong with the "CONTAINS(Category@row, @cell), "EDAP Support Issues"

The formula works without it

My formula

=COUNTIFS([Creation Date]:[Creation Date], @cell >= DATE(2023, 1, 1), CONTAINS(Category@row, @cell), "EDAP Support Issues", (Status:Status, NOT(OR(CONTAINS("Done", @cell), CONTAINS("Canceled", @cell))))

Note : The Categry field is a computed column. I was not sure if the summary metrics ( COUNTIFS) can be based of a computed column

Will really appreciate if anyone can help me

Find more posts tagged with

Accepted answers

All comments

roya

I get the UNPARSABLE error message

heyjay

=COUNTIFS(
Category:Category, "EDAP Support Issues", 
[Creation Date]:[Creation Date], >=DATE(2023, 1, 1), 
Status:Status, <>"Done", 
Status:Status, <>"Cancelled")

Add more status criteria as needed,

Status:Status, <>"Others",

roya

I get an error - #NO MATCH while trying the code above

heyjay

Not sure how you ended up with #NO Match. Can you please check if you have the columns are matching the formula. I might have some type or our columns does not match. Your Creation date column should be set as DATE in the properties.

Please also share a snip of your sheet so we can further investigate.

The formula is working OK on my end.

roya

Thanks so much for your help and support.

The issue is that the CATEGORY field is populated from a formula driven from an EPIC formula (see below)

Then I was trying to count the instances of Category in the SHEET SUMMARY using the COUNTIFS suggested by you. This formula works when If use a new sheet (with no formulas on the Category) , but doesnt work in my existing sheet

=COUNTIFS(
Category:Category, "EDAP Support Issues", 
[Creation Date]:[Creation Date], >=DATE(2023, 1, 1), 
Status:Status, <>"Done", 
Status:Status, <>"Cancelled")

Category Formula

=IF(CONTAINS("Bugs", [Epic Name]@row), "Bugs", IF(CONTAINS("Project", [Epic Name]@row), "Project| New Features", IF(CONTAINS("TechDebt", [Epic Name]@row), "DataOps TechDebt", IF(CONTAINS("A360", [Epic Name]@row), "A360 Request", IF(CONTAINS("Support", [Epic Name]@row), "EDAP Support Issues", IF(CONTAINS("Optimization", [Epic Name]@row), "Data Optimization", IF(CONTAINS("Regression", [Epic Name]@row), "Regression Testing", " ")))))))

EPIC Formula

=INDEX({DataOps Epics Range 1}, MATCH(Parent@row, {DataOps Epics Range Issue Key}, 0))

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.