-
How do I build a formula that looks for duplicates that also ignores blanks or "N/A"?
I'm trying to build a formula that counts if it finds a match so it will check a box but I don't want it to count blanks or when someone enters "N/A". =(IF(COUNTIFS([Document ID #*]:[Document ID #*], [Document ID #*]@row) > 1, 1)) And no matter what I try I seem to break the formula.
-
Countifs with date criteria and also parsing drop down
Hi all, Been struggling on a formula for too long. Need some assistance from some of the great minds. I have taking information from a sheet and trying to count the amount of times a condition is met within a specific time frame. Here is my current formula: =COUNTIFS({IC Issue Tracker Range 7}, @cell >= DATE(2023, 2, 20),…
-
I am trying to count project tasks that are in any status except "Complete" and due in 7 days.
Hi. I'm trying to count tasks due in the next 7 days that aren't completed, but it's still counting past the 7 days. Any help is appreciated! Heres the formula I tried using: =COUNTIFS(Status:Status, <>"Complete", [End Date]:[End Date], AND(@cell >= TODAY(+7)))
-
RYG Health formula based on due date and % complete
I'm trying to create a formula to calculate the below: 'Due date' is in the next 10 days and '% complete' is less than 50% = "Red" 'Due date' is in the next 10 days and '% complete' is less than 90% = "Yellow" 'Due date' is in the next 10 days and '% complete' is greater than 90% or 100% = "Green"
-
VLOOKUP with more than one contact
I need a formula that when a job is booked in, will check to see if team members assigned to it have liability insurance. I have a separate sheet (Certificates v2.0) with our workers listed and whether their liability is Current or Expired. The following formula works but only when there is a single team member in the Team…
-
I need help with a sumifs when a column contains a value and when a date falls within 2023
Hey everyone. This is where I think I need your hive mind. This is what I think my calculation should be like when I am trying to sum when a column is contains a number and the date range is in 2023. Can you help me cause its not working. SUMIFS([Planned Savings]:[Planned Savings], "", [End Date]:[End Date], <=DATE(2023,…
-
Dashboard Filtering
In excel I can build dashboards with a data drop down list that contains a list of areas, at the top of the dashboard and loads of graphs linked to it ... I have 2nd sheet (Data Summary) that has cell A2 =(Dashboard dropdown) to provide data, with formulas that data changes if it matches cell A2. (It all comes from a 3rd…
-
(Index(Collect
I am using the (Index(Collect formula as well and it is telling me that my formula is "#UNPARSEABLE". Could someone help me with this? I tried using the following format "INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2")", but it unfortunately stopped…
-
Track Birthdays and Anniversaries coming up
I am trying to set up a dashboard to allow me to see upcoming Birthdays and Anniversaries, with them being sorted to earliest upcoming being at the top of the list. I tried, what I thought would have been to do a min collect, without luck. Then I thought maybe someone had a better idea as to how to make the happen.
-
Pull latest date before today
I am trying to pull the latest date a document was reviewed. The review dates are on separate sheet and each document has an ID# (there is an ID# column). There are different types of reviews which is listed in the Parent Row and I have the collect function pulling specific review types. I was able to achieve this with the…
