-
IF Statements with Dates
Hi all, I am clearly missing something… I have a sheet with columns for the Start and Date Ends and then another column to track the Status (In Progress, Enrolled/Upcoming, Completed). The formula is currently: =IF([Start Date]@row > TODAY(), "Enrolled", IF([Start Date]@row < TODAY(), "In Progress", IF([End Date]@row <…
-
SUMIFS cross-reference date ranges
This is my formula. It's "working" in the sense that it doesn't return an error but it returns a 0 value for each instance when I know some of the values should be non-zero. Any advice is appreciated. =SUMIFS({Consortium HC}, {Consortium Start Date Range}, >=[Week Start]@row , {Consortium End Date Range}, <=[Week End]@row)
-
COUNTIF formula for number of boxes checked is not working
I have a very simple need to generate a count of how many boxes are "marked for disposal" via counting how many cells boxes are checked in a certain column, I have attached a screenshot. The formula is not working, it has given me a count of 0 when placed in text only cell and it does not give me anything when it is in the…
-
Need help with IF AND statement using symbols
I need help with using symbols with formulas. The column property is set to symbols. My current working formula is: =IF(TODAY() > [Remedy Deadline]@row, "Red", IF([Finding Status]@row = "Finding is Closed", "Green", "Yellow")) However, my first IF condition needs to satisfy two conditions: (1) (TODAY() > [Remedy…
-
Sum Calculation including a date range
I need to figure out a formula that looks at column ranges 23-Jan to 27-Jan and sums the totals highlighted in blue, if the date range is within a rolling 12 years from the current date. This is the Excel formula that was used (which was more columns): =sumif($DF$4:$GZ$4,">"&Today()-365,DF7:GZ7) I can't figure it out in…
-
Asset Management - Due in 7 Days not working
This is part of the asset management template. The stock formula has a category for assets due in 7 days. I wanted to change that to within 7 days, and I looked up this formula that is supposed to do the trick: =COUNTIF({Sheet - Asset Management Sheet Range 2}, TODAY(7)) However, it is still only returning assets due in…
-
Forms and Formulas
When you are making/editing a form are you able to have a formula to track continually? Like if someone is tracking time per day but they are working on many projects so they are filling out a form for each project. Can the form track the time and show it to the person filling it out for if they were doing multiple…
-
Index Match Parent Rows Only
I've got a cross sheet reference Index Match formula that is working on some cells, but not others. Here's the issue: My source sheet has a list of clients (Company column). I have a helper column in that sheet with a formula to check parent rows. I am trying to pull the Status and Type from that sheet into my Metrics…
-
Formula for counting rows as they flow in and out
Hi Everyone, We have an intake sheet, fed by a form, that assigns rows to staff members. Leadership wants to be able to balance workload by tracking numbers of rows assigned over a timeframe. On a metric sheet, we use =COUNTIFS({Column Name}, "Person Name") to see how many each person has assigned to them at any given…
-
INDEX/MATCH not working for some values
We have been creating a pharmacy inventory system where we scan in items and fill out the relevant information for the items in a form for our incoming stock: Then we made a second sheet for items going out that only includes the barcode. I was then planning to populate the rest of the information using INDEX/MATCH from…