COUNTIF:CONTAINS formula help

Hello!

I'm working on a formula to capture project start dates in each month. The formula I'm using is as follows: =COUNTIFS({Target Start Date}, (CONTAINS("January", @cell)), {Status}, ="In Progress", {Type}, ="Enterprise") I'm not getting an error message with this formula but it doesn't appear to be capturing any of the projects with a January start.

Any help would be appreciated. Thanks!

Answers

  • Paul McGuinness
    Paul McGuinness Overachievers

    Hello @anuelle

    This formula should work for you, made assumption on the column names.

    =COUNTIFS(Status:Status, "In Progress", Type:Type, "Enterprise", [Target Start Date]:[Target Start Date], MONTH(@cell) = 1)

    Hope that helps.

    Paul

  • anuelle
    anuelle ✭✭✭✭

    This doesn't appear to be working either. I don't know if it makes a difference or not, but the columns I'm referencing are on a separate sheet. Would that cause the error I'm having?

  • Hi @anuelle

    Yes, it will make a difference if you're looking into another sheet! You'll need to update each of Paul's [in-sheet ranges] with your own {Cross sheet ranges}, like so:

    =COUNTIFS({Status}, "In Progress", {Type}, "Enterprise", {Target Start Date}, MONTH(@cell) = 1)

    Sometimes you can get an error if the Start Date has text or blank cells, so you can wrap your MONTH portion in an IFERROR to get rid of that:

    =COUNTIFS({Status}, "In Progress", {Type}, "Enterprise", {Target Start Date}, IFERROR(MONTH(@cell), 0) = 1)


    If this still hasn't worked, it would be helpful to know exactly what formula you've tried and what error message you're getting.

    Thanks!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!