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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Use Named Reference for Date in SumIfsHi all. Looking to see if this is possible, as it's proving to be the final obstacle in me getting something done. I'm using SumIfs to sum figures based on a few different metrics, one of which is a <=date. It looks like this: =sumifs(A:A, B:B, 2026, C:C, <DateSelect) Where DateSelect is a cell in which the user enters a date manually. The formula works fine when replacing the DateSelect with typing in the actual date into the formula itself like below: =sumifs(A:A, B:B, 2026, C:C, "<01/02/2026") But I need the formula to work with the date entered by the user. Is there a way for the formula to work with a named reference? submitted by /u/GFDoomTrain [link] [comments]
- Sum formula for calculating month occurrences from dates works for everything except for January.I am attempting to automate the calculations of how often each month is recorded in another tab. The formula is working for all the other months, but as I am using this template to calculate values as I enter them, I arbitrarily set the limit to 2000, allowing me not to have to think about changing the formula as I enter new information. Unfortunately, it seems that the blank cells are being coded as "1" https://preview.redd.it/7zte9xn701vg1.png?width=972&format=png&auto=webp&s=ba131bec322f84ce55fa9fdbc35cedfcbeb12d9f I have tried going through the formula and found that all the blank cells are coding themselves as "true" only for the month of January (1). I attempted to add =IF(SHEET2!A3:A2000 = " ", 0, ... to the beginning of the formula, but it just gave me a spill error. I might be getting confused between excel vs. Google Sheets formulas. Thank you! submitted by /u/mentallylost14 [link] [comments]