COUNTIF(CONTAINS formula help

anuelle

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!

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:

1. 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")
2. {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).