-
Formula to populate a "Year" if Create date falls between 2 column dates
I created a form that request the Calendar year for funding. They range from 2022 to 2028 and each year has Quarters. Sample quarter is between April 15 - July 15 so if I want the formula to populate me a Calendar year 2022 if the create date falls between the April - July dates. Here is the formula that works but is…
-
Count Days Since Ticket Opened
Hello, Needing to calculate number of days since line item appeared when Date Submitted column is "Auto-Number/System Generated Column Created (Date)" -- and then display "Closed" when Checkbox is checked in Complete column. Can't get the "Complete" to appear -- just goes blank. =IFERROR(IF(Complete@row = 0, TODAY() -…
-
Using Index Collect formula when there are multiple matches in the source sheet. (#Invalid Value)
Hello, My goal with using INDEX(COLLECT)) was to obtain information based on 3 different criteria, but in my source sheet there are multiple matches for the three criteria specified. What do I need to add to the Index, Collect formula to overcome this or do I need to use a different formula combination? Below is the…
-
Total sum of results of a formula?
I have a formula to calculate payouts as follows: =IF(AND([Estimated New Annual (ACV) Net Booking $ Amount]@row >= 75000, [Estimated New Annual (ACV) Net Booking $ Amount]@row <= 250000), "$2500", IF(AND([Estimated New Annual (ACV) Net Booking $ Amount]@row >= 251000, [Estimated New Annual (ACV) Net Booking $ Amount]@row <…
-
Red Flag for Overdue Tasks, without also Flagging non-dated ones
We are trying to use a formula to do the following: •tasks that have been cancelled are gray •tasks that are past their due date are red •tasks whose due dates come up within the next 5 days and are not yet complete are in Yellow •tasks which are already completed or whose start date hasn't hit, and are due in 14 days are…
-
Combined IF, AND, & OR Statments
I am having trouble with a combined IF, AND, & OR formula. The formula is to check five columns for a Yes "Y" value. For two of the columns only one value has to be Yes. If all are true then return a 1, if not then return a 0. Where is my formatting wrong? =IF(AND([SPC Received]@row = "Y", OR([SDS Received]@row = "Y",…
-
Formula to create symbols based on parameters
Hello Community, I'm attempting to create a formula that will create a green, red, or yellow dot symbol based on parameters and values within given cells. The formula I have thus far is below but I continue to receive error messages Any help would be appreciated. Thanks!
-
COUNTIFS across columns with multiple options for one column
I am trying to write a formula to count the number of columns across five nonadjacent columns that contain "Verified". The fifth column may contain "N/A" and also needs to be counted. In the example, 5 should be the total count for both rows. I've tried adding multiple COUNTIFS, and it doesn't work. Thanks in advance for…
-
Creating Archive Files In Smartsheet
While rewriting my 2016 Smartsheet application that tracks all sorts of weekly and monthly data on projects and employee billable hours charges, I needed to be able to store those results in archive files for use by various sheets and reports that use that data. Automated workflows allow that data to be copied to another…
-
Quarterly Budget with SUMIF
Good Morning, I am trying to use the SUMIF function to pull data from another sheet. =SUMIFS({Budget Requests Range 1}, {Budget Requests Range 2}, "Software", {Q/Y Lookup}, [Q/Y]@row, "") The other content is: Budget Amount (Budget Requests Range 1) Type of Request (Budget Requests Range 2) Q/Y that the budget applies to…