COUNTIF(CONTAINS formula help

Hello,

I need some help with a formula I'm using to track project start dates. For example, if the project is slated to start in January, has a status of In Progress, and it's type is Recurring Annually. The formula I'm currently using is: =COUNTIFS({Target Start Date}, CONTAINS("January", @cell), {Type}, ="Recurring", {Status}, ="In Progress") however this formula returns a value of 0.

A few things to note in case it makes a difference: the target start column is a date column formatted to display January 1, 2022. The target start column is also on a separate sheet from the one the formula is going to be on.

Thanks!

Find more posts tagged with

Accepted answers

All comments

MedaUser

Hi!

Are you able to provide a screenshot of your sheet, so I can provide an accurate answer?

My initial thought is the formula should read: =COUNTIFS([Target Start Date]:[Target Start Date], IFERROR(MONTH(@cell), 0) = 1, Status:Status, "In Progress", Type:Type, "Recurring Annually")

anuelle

This is the sheet that will have the formula

MedaUser

Thanks! Since I can't see it in the screenshot, do you have columns for Status, Type, and Target Start Date? If so, does the formula I provided work?

Keep in mind that you'll need to update this part of the formula {[Target Start Date]:[Target Start Date], IFERROR(MONTH(@cell), 0) = 1} for each respective month (i.e. January = 1, Feb = 2)

anuelle

Unless I'm missing something, the formula isn't working. Here's what I have: =COUNTIFS({Target Start Date}, IFERROR(MONTH(@cell, 0) = 1, {Status}, "In Progress", {Type}, "Recurring")).

Also, here's a screenshot of the second sheet I'm pulling the type, status, and start date from

MedaUser

From what I can tell, the problem right now is due to 2 areas:

Using {Column Name} will not work as that isn't the correct command. You have to use [Column Name]:[Column Name] like I have in my previous comment. Try copy/pasting this into your cell and see if it works: =COUNTIFS([Target Start Date]:[Target Start Date], IFERROR(MONTH(@cell), 0) = 1, [Project Status]:[Project Status], "In Progress", Type:Type, "Recurring")
{Status} is not a column name in your screenshot. This would need to be updated to Project Status in the formula.

anuelle

Since the formula is on a separate sheet, doesn't the column it's referencing have to be in those brackets? { } I tried copying your formula and am still getting an error.

MedaUser

Oh right! I forgot you mentioned you're referencing another sheet. Whatever is in the brackets is the name of the reference. If you didn't rename the reference, it typically defaults to {Sheet Name - Range 1}. When you are trying to map it, I would suggest removing that part of my formula that is the range and click "Reference Another Sheet">Find the sheet>select the entire column range you are referencing and insert the reference. Be sure you keep the criterion included (i.e. Recurring).

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.