-
If Function ERROR
=IF(AND([Term length (Years)]@row >= 1, [Term length (Years)]@row <= 5, Referral@row = "External"), 0.03, IF(AND([Term length (Years)]@row >= 6, Referral@row = "External"), 0.03, IF(AND([Term length (Years)]@row >= 1, [Term length (Years)]@row <= 5, Referral@row = "None"), 0.07, IF(AND([Term length (Years)]@row >= 6,…
-
NETDAYS based on IF statement
I'm trying to use a NETDAYS function to determine how many days overdue a payment is, but I only want to display that number of days if a column named 'Status' has the value 'Client payment pending'. The function I'm trying to use is =NETDAYS([Date Sent to AP/CR]398, TODAY()) but I don't know how to work that into a…
-
SUMIFS - Contains value in cell from another sheet
Hi All, I want to create a "Total Vendor Paid Amount" column on our "Main Dashboard". This column will need a formula that pulls information from another dashboard called "REO Vendor Invoicing (3)". I want the formula to sum the total invoices paid, in the "REO Vendor Invoicing (3)" dashboard, of jobs that contain a cell…
-
Countif - Want to count number of times a word appears, multiple then divide by a certain number
I'm creating a pricing sheet and need to use the countif formula. I am counting the number of times "High", "Moderate", "Low" appears in the column and times by 3, 2 or 1 then dividing it by the total count. For the sake of space, I named my column "G". Not sure if Smartsheet recognizes the "+" in the formula…
-
Newbie Question - Reports Lookup into Sheet (relational database like)
First, I am sure this has been answered, but I could not find it. I have two smartsheets: Projects and PMs. The PMs sheet has basic info about the PM (Emp ID, Name, Email). The project sheet has all the projects we are working in the company with lots of project metrics. One being Margin %. I want to create, then run a…
-
Unparseable Error
I am trying to create a formula that will return different values based on multiple factors, and the information on the sheet is linked in from another sheet my sheet is attached and the formula I am trying to use is below. Any help is greatly appreciated Here is my formula =IF([% Complete]1 = 1, "complete",),if(and([%…
-
Can I have the default value be a function?
I have a form that asks for someone's name. Most of the time, the form is for that person. There are some cases where a person might fill out the form on behalf of someone else. What I'd like to do is have a "name" field and a "on behalf of" field. If "on behalf of" is blank, I'd like it to default to the value of "name".…
-
What's wrong with my RYGB formula?
Ok, I'm feeling really stumped. I have this formula for changing the color of the RYGB balls based on the number in a specific cell but it is returning an #INVALIDOPERATION on anything except zero (when the ball turns blue). Where am I messing this up? =IF([# of Days from Submission to Assignment]@row = 0, "Blue", IF([# of…
-
Sumif WTheck?
Hello - I am trying to draft a formula to sum the column 9 but only when column 3 is "John Smith" and when column 11 is not equal to "complete." I got this far, but can't figure this out: =SUMIF([Column9]:[Column9],Column3; column3, @cell="John Smith")
-
Adding IF to a COUNTIFS
I have a formula to count based on multiple criteria which works correctly. =COUNTIFS([Change Originator Link]:[Change Originator Link], [Change Originator]@row, [DCO Status]:[DCO Status], NOT(OR(@cell = "Implemented", @cell = "Cancel"))) However, if the Change Originator (column name) for that row is blank, I would like…
