-
Formula - Adding a Column
Today, I created the following formula in a cell =IF(AND([Roommate in Room?]@row = "Yes", [Requested Bed Type]@row = "Double"), ".5", IF(AND([Roommate in Room?]@row = "No", [Requested Bed Type]@row = "Double"), 1, 0)) As you can see one of the variables returns a .5. The formula works great until I go to my sheet summary…
-
Combine Countifs and OR formulas
I need to get a count of coworkers taking PTO on a specific week. My current sheet counts the week number PTO starts and ends (separate column for each). Is it possible to combine a COUNTIF and OR formulas? A specific example of where I'm stuck is if someone took a long weekend from 4/1/22 to 4/4/22. My sheet will show…
-
Use Formula to Check Checkbox as a Column Formula
I found this formula in the community that works perfectly to check the box if there is any text in certain cells. =IF(OR(ISTEXT([Column1]@row), ISTEXT([Column2]@row), ISTEXT([Column3]@row), ISTEXT([Column4]@row)), 1, 0) I'm getting a column formula syntax error when I try to apply it to the whole column: "The column…
-
Hierarchy issues
Hi everyone, I have been pursuing the forum but I can't seem to find the answer. For some background, my company had a team that set up our Workspace and sheets. One of my coworkers was also the admin but recently left for another job. I have been tasked with taking over admin/owner duties now. I understand the basics and…
-
would like to add an IFERROR to a simple formula, can't seem to get it to work
Hi, I have a simple formula I am using in one of my sheets to calculate a percentage: =[Training/90 day complete]@row / FDS@row I would like to amend it so that if the cell in the Training/90day complete column is blank, to leave it blank rather than calculating it and returning 0%. How would I do that? Thanks in advance!…
-
Nested Or within Countifs formula
Hi, I am hoping to see if I can get help with the following formula: =COUNTIFS([Area of focus]:[Area of focus], "HGIT", Status:Status, "Complete", OR([Status:Status] = "Approved"), [Channel:Channel], <>"internal comms") Essentially, my goal is to count items that are for "HGIT" that are either complete or approved but not…
-
Formula to Roll Up Task Status
Hi All! I am looking for some help with a formula that I haven't been able to find in the community already. I'd like to roll up my Status column to the parent rows with the following conditions: Parent shows as Not Started if: All children are either Cancelled or Not Started. Parent shows as In Progress if: Even one child…
-
Calculate date diff from previous row
Hello Smart People - Simple question. I would like to keep a running tally of the time between dates in the same column as it progresses via rows, so [Date]@row - [Date]@PreviousRow. Make sense? I think in SQL we just use "Previous". Thanks, Mark
-
INDEX(COLLECT #INVALID VALUE error
Hi! I'm trying to solve a formula issue. I'm trying to pull text (Title) from a source sheet based on 2 criteria: Slot and Score. The slot needs to equal 1.1, and the score needs to match adscore@row. Here's the formula I created: I am getting a #INVALID VALUE error message. Other background information: {Title}, {Slot},…
-
Formula previously worked, doesn't now
Hello! This logic seemed to work in a different capacity, but no longer is. I have the three categories used locked to check box only. Help welcome! =IF(AND([Sent to VP for Signature]@row = 0, [Returned to Requester]@row = 0, [Fully Executed Filed]@row = 0), “Red”, IF(AND([Sent to VP for Signature]@row = 1, [Returned to…