CountIF - Boolean Expected
I'm trying to use two countifs formulas to determine if the "last contact" is within 30 days. However, I need to know if it is in the last 30 days based off of two parameters (either in last 30 days of EE contact OR Consultant contact). below is the formula I'm using and a copy of my sheet.
Based on what I read on the community, I'm getting the "boolean" error because I am using a checkbox column. However, I'm unsure how to correct, as i would like to continue using the checkbox. I'm thinking my formula could be improved, just not sure how.
=COUNTIFS([OD - Last Contact]@row, <TODAY(1), [OD - Last Contact]@row, >TODAY(-30)) + COUNTIFS([Consultant - Last Contact]@row, <TODAY(1), [Consultant - Last Contact]@row, >TODAY(-30))
Best Answers
-
You would use an IF statement to say that if the count is greater than zero, check the box.
=IF(COUNTIFS([OD - Last Contact]@row, @cell < TODAY(1), [OD - Last Contact]@row, @cell >TODAY(-30)) + COUNTIFS([Consultant - Last Contact]@row, @cell < TODAY(1), [Consultant - Last Contact]@row, @cell > TODAY(-30)) > 0, 1)
-
Happy to help. 👍️
The usual indicator for an OR statement for me is when I start repeating the same output. If I have two different portions of a nested IF outputting the same thing, then I try to combine them with an OR statement to help keep things organized.
While technically you don't HAVE to in some cases (this one being one of those cases) and you could use the nested IF if you wanted to, I personally prefer to keep things as grouped together as I can.
Answers
-
You would use an IF statement to say that if the count is greater than zero, check the box.
=IF(COUNTIFS([OD - Last Contact]@row, @cell < TODAY(1), [OD - Last Contact]@row, @cell >TODAY(-30)) + COUNTIFS([Consultant - Last Contact]@row, @cell < TODAY(1), [Consultant - Last Contact]@row, @cell > TODAY(-30)) > 0, 1)
-
As always, @Paul Newcome to the rescue. One additional question, if I want to add an additional parameter, i.e. the assessment (green, yellow, red) to the formula, how would i do that? I'm trying to say, if the assessment is red or yellow, then 1, if it is green, then use the in the last 30 days parameter. I think i might just have my parentheses in the wrong spot but I'm tried moving it around and I'm still getting an error.
=IF(Assessment@row="Red", 1), If(Assessment@row="Yellow",1), IF(Assessment@row = "Green", (COUNTIFS([OD - Last Contact]@row, <TODAY(1), [OD - Last Contact]@row, >TODAY(-30)) + COUNTIFS([Consultant - Last Contact]@row, <TODAY(1), [Consultant - Last Contact]@row, >TODAY(-30)) > 0, 1))
-
We can actually use an OR function,
=IF(OR(Assessment@row = "Red", Assessment@row = "Yellow", countifs_criteria > 0), 1)
=IF(OR(Assessment@row = "Red", Assessment@row = "Yellow", COUNTIFS([OD - Last Contact]@row, @cell < TODAY(1), [OD - Last Contact]@row, @cell >TODAY(-30)) + COUNTIFS([Consultant - Last Contact]@row, @cell < TODAY(1), [Consultant - Last Contact]@row, @cell > TODAY(-30)) > 0), 1)
-
I need to learn how to how to better use "or" and "and" functions. Thanks a ton!
-
Happy to help. 👍️
The usual indicator for an OR statement for me is when I start repeating the same output. If I have two different portions of a nested IF outputting the same thing, then I try to combine them with an OR statement to help keep things organized.
While technically you don't HAVE to in some cases (this one being one of those cases) and you could use the nested IF if you wanted to, I personally prefer to keep things as grouped together as I can.
-
New here and need some help if possible on a formula. I have two columns of dates, A and B that I need to count the number of occurrences between the 1st of the month and the end of the month in the column A, however if it is blank than use the date in column B. I am struggling with the IF blank, then get date from column B.
=COUNTIFS({A}, >=DATE(2021, 1, 1), {A}, <=DATE(2021, 1, 31), IF({A}, "ISBLANK", {B}, >=DATE(2021, 1, 1), {B}, <=DATE(2021, 1, 31)))
-
@Tina Davis Try something like this...
=COUNTIFS({A}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021)) + COUNTIFS({B}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021), {A}, @cell + "")
The above says to count all of column A that is for month 1 and year 2021, then count column B for the same criteria where column A is blank, and then add the two counts together.
-
You are my new hero Paul, thank you so much:)
Help Article Resources
Categories
Check out the Formula Handbook template!