Using COUNTIFS with CONTAINS

deb_63_hydracor

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!

Jeff Reisman

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"))

Jeff Reisman

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(...

deb_63_hydracor

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

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"))