Having an issue with COUNTIFS with multiple criteria and referencing another sheet.
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.
Best Answer
-
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.
Answers
-
$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.
-
@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.
-
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.
-
@jmyzk_cloudsmart_jp Thank you, you rock! This worked perfectly and I learned so much from this thank you.
-
Help Article Resources
Categories
Check out the Formula Handbook template!
