-
CountIFs to count multiple items in same column
I want to count the instances of both "Complete" and "Not Applicable" in the same column. I got it to work with just "Complete" then divide by the total rows to make a percentage using this formula: =COUNTIF(Status:Status, "Complete") / COUNT(Status:Status) But when I add the other variable the formula does not work, I…
-
Formula using WORKDAY, TODAY, ISBLANK - #INCORRECT ARGUMENT
I want to calculate the number of days that a ticket is in Backlog. [Ticket Status]@row = "Backlog" WORKDAY([Date Submitted]@row, TODAY()) is the number of days from the date of submission to current date. I realize that my Date Submitted cell sometimes is blank so I added: NOT(ISBLANK([Date Submitted@row]) check. Now it…
-
Sumifs syntax help
trying to get a sum for the number of OT sessions for each student in a given week not sure what's wrong with my formula =SUMIFS([Todays Week ]@row, {Week Number}, [Student Code]@row, {OT Student Code}, {OT Sessions},=>1) I'm trying to sum the number in the OT session column of a separate sheet if the week number matches…
-
Evaluate if a Date column is blank
I have three columns with the below formula in the "Helper - Overdue" column. This works correctly for when there is a date in the "Deadline" column and the "Item Completed" box is not checked. It also works correctly for when the box is checked. The part that is not working is when the date column is blank, it shows as…
-
Total % Complete for 4 columns
I think this formula should be easy but I am terrible with formulas. There are 4 columns, when there is a date = 25%, if there is no date =0% and the final column that will show total % complete for the 4 columns: Formula I have is: =SUM([LAN MIGRATION COMPLETE2]@rowDATE([LAN Migration Ready Actual]@row, 0.25) +…
-
How to setup an "If" with dates involved?
Pretty new to the SS scene. I am looking for assistance/guidance on how to create a formula to check the current date, and compare it to another date in a different column and if the difference is greater than XXX days have it provide a symbol. I have tried googling and have only found a bunch of functions:…
-
Stumped on a SUMIFS for Fiscal Year (FY)
Hello everyone, I am a bit stumped on a solution. I tend to not like to hardcode information into a formula. I was working through a solution to help manage a Business Development (BD) pipeline. With the help of the community, I figured out how to do a YTD. But then I was thinking that it didn't make sense to have the BD…
-
IF statement inside a COLLECT
Why do all of these formulas work: SUM(COLLECT({Value}, {Location}, Location@row)) SUM(COLLECT({Value}, {Location}, <>"")) SUM(COLLECT({Value}, {Location}, IF(index@row=1, "Boston", Location@row))) But this does not: SUM(COLLECT({Value}, {Location}, IF(index@row=1, <>"", Location@row))) I'm trying to understand this…
-
Counting Number of Boxes Checked in a Single Row
Hi all! I am wondering if there is a way to count the number of times a box has been checked in a single cell. For example, I have a row that shows the years that an employee participated in a program (2015-2022). You can select multiple because they can stay in this program for many years. Then I would want a formula to…
-
Finding the last highest value, and subtracting from @row value
Hi, I have a sheet where CNC operators enter the hours the machine has worked each day, and i want to avoid them having to reset the time clock eacht time. I have been struggling with a formula that helps me achieve the following objective: Find the highest number of hours entered for the same machine on the current row…
