Can't find correct place for quotation marks with COUNTIF criteria including mathematical operators and named variable
Here’s a section of my table called Visits, which has 5,585 rows:
The fiscal year begins on July 1, so the formula in F (Day_of_FYE) is =[@[Visit_Date]]-DATE(VALUE([@FYE])-1,7,0).
In another tab I’m trying to figure out how many visits in previous years occurred at the same point in the current year. I have two names defined for the worksheet:
CurrentFYE =IF(MONTH(TODAY())<7,YEAR(TODAY()),YEAR(TODAY())+1)
DayOfCurrentFYE = (TODAY()-DATE(VALUE(CurrentFYE)-1,7,0))
I know that today is day 280 of the current fiscal. If I use the formula =COUNTIF(Visits[Day_of_FYE],"<=280") I get 3,977, which is correct. But I can’t figure out how to use the mathematical operators with the name of the variable.
If I write =COUNTIF(Visits[Day_of_FYE],"<=DayOfCurrentFY") the result is 0.
If I write =COUNTIF(Visits[Day_of_FYE],"<=”DayOfCurrentFY) I get the message “There’s a problem with this formula”.
Is there no way to use names in criteria like this? Or am I overlooking something else?
[link] [comments]
Want to read more?
Check out the full article on the original site