-
Sumifs with multiple criteria options within same criteria range
I am trying to write a formula that will count the # of days that are considered "Time Off". I've put a filter on my source sheet with the needed criteria and need my formula to give the # 11. Essentially - look at Sheet (Field Movements). Give me a sum(total) of the "Da..." column (which this would equal 11) Criteria…
-
Search a data column and return the most recent past Saturday.
Have not found this in the community, videos, courses, or formula template. CONTEXT: I have a dashboard that updates weekly. It has weekly data and a rolling 13 week chart. The date column is a Saturday (weekending date) and the team completes it when they come in Monday for the week prior). I have a data collection sheet…
-
Average if date falls in a month and contains certain text
Still learning average formulas! Trying to come up with a formula to average the number of items where the despatch date is in a certain month (ie January - not of a particular year but all January's) and if another column contains certain text. So it will average the number of items in January if the country column is…
-
Cross-sheet references with Index/Match and reference sheet has multiple rows that match
Sheet A: Formula in Data: Ent. Install Time: =IF(CONTAINS("Enterprise 2022 R1", {AtRiskAnalysis-Version_Version}), INDEX({AtRiskAnalysis-Version_EntInstallTime}, MATCH(CustomerId@row, {AtRiskAnalysis-Version_CustomerID}, 0))) Formula in Data: EDIS Install Time: =IF(CONTAINS("EDIS 2022 R1",…
-
Formula to split up / break down long strings of text
Hi, Hope you're all keeping well. I was hoping someone could point me in the right direction of a formula / function that can help break down large sections of text. If someone could point me in the right direction, I should be able to work out the nuances and implementation myself. I have an API that exports data into a…
-
Activate/Deactivate a Checkbox
Is it possible to activate/deactivate a checkbox, based on the value in another cell? For instance, I have a sheet where users enter data via a form. Then there is a "DQ_Check" column with an IF statement that returns "TRUE" or "FALSE" to indicate that all required fields are valid. If that statement returns true, I want…
-
IF/AND formula
I need to calculate the number of Network Days based on a Leave Status but if a half day has been checked, it needs to just put 0.5. The formula I am currently using is: =IF(AND([Half Day]@row = 1) [Leave Type]@row = "TOIL", "0.5", NETWORKDAYS([Start Date]@row, [End Date]@row)) But I'm getting a #Unparsable error. What am…
-
i need help creating a formula
I have this row with dropdown options that contain the Quantity and the Price. I want the formula at the end to generate the Total $ for that row so the customer knows the total for the items they want. How do I do this?
-
Formula Issue - Invalid Operation
=IF([Time Due]@row > [Day End]@row, [Day Start]@row + ([Time Due]@row - [Day End]@row), 0) Getting an "Invalid Operation" with the above and cannot seem to see it. I really think I have been looking at this Ticketing solution for too long.
-
Help translating Excel formula to Smartsheet formula
I need some help translating a formula from Excel into something that I could use with Smartsheet. The final formula in Smartsheet would end up being a nested if: If Enterprise 2022 R1, give # of months between GA date and Go-live date, if EDIS 2022 R1, give # of months between EDGA Date and Go-live Date, if these are…
