Using COUNTIFS with CONTAINS

Options
deb_63_hydracor
deb_63_hydracor ✭✭✭✭✭✭
edited 06/20/22 in Formulas and Functions

I am trying to count the number of rows whose tier contain "Plus" and are within a specified date range. I have this formula which works for a specific tier (not using "contains"):

This formula works:

=COUNTIFS({Tier}, "Experiencia", {Original Launch Date}, AND(@cell >= DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))


This formula comes back unparseable:

=COUNTIFS({Tier}:{Tier}, CONTAINS("Plus",@cell)), {Original Launch Date}, AND(@cell >= DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))


Thanks for your help!

Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Check the color-coding with your parentheses. Looks like you're closing off the COUNTIFS too early with an extra close parentheses after the CONTAINS("Plus",@cell)). Smartsheet's color-coding makes it so that the color of the open parentheses after COUNTIFS should match the one at the very end of the COUNTIFS statement, in this case at the end of the formula. Try this:

    =COUNTIFS({Tier}, CONTAINS("Plus", @cell), {EHR Go-Live Date}, AND(@cell >= DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))

    See the link to Formula Error Messages in my signature. That page can help you figure out what's wrong based on the error message you get. For #INVALID DATA TYPE, check to make sure you're not trying to use a function against a column with an incompatible data type (Make sure the column referenced in {EHR Go-Live Date} is an actual date-type column.) In your criteria for {Tier} inside the OR, you should try using @cell = for each value. And lastly, you need to fully close the AND before going on to your next criteria set in the COUNTIFS (see the inserted parentheses after DATE(2022, 1, 31). Again, this is where examining the parentheses color-coding comes in handy. Try this:

    =COUNTIFS({EHR Go-Live Date}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)), {Tier}, OR(@cell = "Experiencia Plus", @cell = "Experiencia Plus Unlimited"))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    I think this is the issue. The reference of {Tier} includes the whole remote column called Tier. Listing it like it was a local column is what's messing things up. Just use =COUNTIFS({Tier}, CONTAINS(...


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • deb_63_hydracor
    deb_63_hydracor ✭✭✭✭✭✭
    Options

    Hi Jeff,


    Unfortunately, that didn't work. Still giving me an unparseable error.

    =COUNTIFS({Tier},CONTAINS("Plus",@cell)), {EHR Go-Live Date}, AND(@cell >= DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))


    I have another formula that I tried but it gives me an Invalid Data Type error.

    =COUNTIFS({EHR Go-Live Date}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31), {Tier}, OR("Experiencia Plus", "Experiencia Plus Unlimited")))

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Check the color-coding with your parentheses. Looks like you're closing off the COUNTIFS too early with an extra close parentheses after the CONTAINS("Plus",@cell)). Smartsheet's color-coding makes it so that the color of the open parentheses after COUNTIFS should match the one at the very end of the COUNTIFS statement, in this case at the end of the formula. Try this:

    =COUNTIFS({Tier}, CONTAINS("Plus", @cell), {EHR Go-Live Date}, AND(@cell >= DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))

    See the link to Formula Error Messages in my signature. That page can help you figure out what's wrong based on the error message you get. For #INVALID DATA TYPE, check to make sure you're not trying to use a function against a column with an incompatible data type (Make sure the column referenced in {EHR Go-Live Date} is an actual date-type column.) In your criteria for {Tier} inside the OR, you should try using @cell = for each value. And lastly, you need to fully close the AND before going on to your next criteria set in the COUNTIFS (see the inserted parentheses after DATE(2022, 1, 31). Again, this is where examining the parentheses color-coding comes in handy. Try this:

    =COUNTIFS({EHR Go-Live Date}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)), {Tier}, OR(@cell = "Experiencia Plus", @cell = "Experiencia Plus Unlimited"))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!