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

jmyzk_cloudsmart_jp

Hi @Dustin AK Lean VA

$0.00, $5.00, etc., can change with formats.

So I would set the criteria as follows;

•  VALUE(@cell) = VALUE([Parent Co Pay Amount]@row)

Please look at the demo dashboard to check how the formula works.

https://app.smartsheet.com/b/publish?EQBCT=d13a36d3a0484b3f80f6b2caa6e9ade3

Dustin AK Lean VA

@jmyzk_cloudsmart_jp Thank you so much, I had been going in circles on this one, that worked perfectly.

I do have another question, that would add onto this. Is there a way to make this work with something like the bottom three. I am making an overview calculation sheet for a client. Your formula helped me perfectly with the top 4. But the bottom three are grouping ones. Or would this just need to be broken down into separate numbers? I tried the formula you gave me on those but I got #INVALID VALUE. If all else I will just break them down and let the client know it is to complex to do it that way with a formula. Just wondering if it would be possible.

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

Dustin AK Lean VA

@jmyzk_cloudsmart_jp Thank you, you rock! This worked perfectly and I learned so much from this thank you.

jmyzk_cloudsmart_jp

@Dustin AK Lean VA

Happy to help.

Good luck!