Having an issue with COUNTIFS with multiple criteria and referencing another sheet.

Dustin AK Lean VA

So, I am having an issue getting COUNTIFS to properly calculate. What I am trying to do is have it count rows with a status of TARP and a co-pay of $0.00. This is the current formula.

=COUNTIFS({PARENT ACTIVE 2a Range 1}, CONTAINS("TARP", @cell), {Parent Co Pay Amount}, CONTAINS("$0.00", {Parent Co Pay Amount})) Now technically this is giving me the correct count for my sheet which is 25. But when I copy it down the the next to and change the co pay to $5.00 and $10.00 it is still saying its 25, which is should be 10 and 0 respectively. So:

=COUNTIFS({PARENT ACTIVE 2a Range 1}, CONTAINS("TARP", @cell), {Parent Co Pay Amount}, CONTAINS("$0.00", {Parent Co Pay Amount})) should be 25.

=COUNTIFS({PARENT ACTIVE 2a Range 1}, CONTAINS("TARP", @cell), {Parent Co Pay Amount}, CONTAINS("$5.00", {Parent Co Pay Amount})) should be 10.

=COUNTIFS({PARENT ACTIVE 2a Range 1}, CONTAINS("TARP", @cell), {Parent Co Pay Amount}, CONTAINS("$10.00", {Parent Co Pay Amount})) should be 0.

But because they are all showing 25 it makes me think the first one isn't working right either and it just happened to be 25 but it is not counting what it should be.

Now in my testing I have come across 2 issues. Separating the formula to just test and see if they are counting correctly. If I don't run the first part like this =COUNTIF({PARENT ACTIVE 2a Range 1}, CONTAINS("TARP", @cell) the count is wrong, without using the contains and @cell. Like if I were to just do this: =COUNTIF({PARENT ACTIVE 2a Range 1}, "TARP").

I tested the back half like this, =COUNTIFS({Parent Co Pay Amount}, CONTAINS("$0.00", @cell) and its always 0 for some reason, I have to do it like this =COUNTIFS({Parent Co Pay Amount}, CONTAINS("$0.00", {Parent Co Pay Amount}) to get the correct count just trying it on it's own.

I tried the first part like I did the back half just to play around and see, and if I do this: =COUNTIFS({PARENT ACTIVE 2a Range 1}, CONTAINS("TARP", {PARENT ACTIVE 2a Range 1}), it is always 0 as well, this part only works with the @cell in it. Which I thought was strange.

I have gone through a lot of other post and comments trying so many things and I am just stuck at this point. Any thoughts would be great.

jmyzk_cloudsmart_jp

Hi @Dustin AK Lean VA

If you want to specify a lower and upper limit for Parent Co Pay Amount and count the corresponding number, in the COUNTIFS Sheet, for example, use the current Parent Co Pay Amount column as an Exact Match or lower limit column, and to indicate the new upper limit add Parent Co Pay Amount: Upper as a column and add the upper values, such as 250 for the lower limit of 50, 500 for the lower limit of 250, and so on.

If there is no upper limit, use the existing formula as Exact Match, and if the upper limit is a number, change it to the following criteria that check the lower and upper limits.

{Parent Co Pay Amount}, VALUE(@cell) > VALUE([Parent Co Pay Amount]@row), {Parent Co Pay Amount}, <=VALUE([Parent Co Pay Amount: Upper]@row)

The formula for the exact match and the one with lower and upper limits is as follows: IF statement.

=IF(ISNUMBER([Parent Co Pay Amount: Upper]@row), COUNTIFS({PARENT ACTIVE 2a}, "TARP", {Parent Co Pay Amount}, VALUE(@cell) > VALUE([Parent Co Pay Amount]@row), {Parent Co Pay Amount}, <=VALUE([Parent Co Pay Amount: Upper]@row)), COUNTIFS({PARENT ACTIVE 2a}, "TARP", {Parent Co Pay Amount}, VALUE(@cell) = VALUE([Parent Co Pay Amount]@row)))

I have updated the demo dashboard to reflect the counting by specifying a range, so please check.

https://app.smartsheet.com/dashboards/q7PQ7FJQm4XVcwMrJJCFjqHMhxGG3w7C9HpJJFg1