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!

Answers

  • MedaUser
    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
    anuelle ✭✭✭✭

    This is the sheet that will have the formula


  • MedaUser
    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
    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
    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
    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
    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).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!