-
SUMIFS
I am trying to create a SUMIFS formula and having issues. I would like to get the SUM by Segmentation: Total SUM of Annual REV If Segmentation is 'Diamond' Total SUM of Annual REV if Segmentation is 'Gold' Total SUM of Annual REV if Segmentation is 'Silver'
-
Setting "Date Completed" column date based on status change
Hi! I'm trying to set up a formula for my Date Completed column that pre-populates the date in that field to the date where the Status on the same line = "Complete". Right now I'm using this formula: =IF(OR(Status125 = "Complete"), TODAY(), "") One problem is that it doesn't add the formula for newly added line items. Also…
-
Multiple IF Formulas - cannot figure out why it's producing a "0"
Hey all - I'm working on a formula that built as an IF formula. It has multiple "IF" statements, and I'm stumped as to why it's producing a zero (although it's likely because there are too many "IF" formulas) and what to use in leu of this formula. I want the formula to put out an abbreviation based off the team name -…
-
POWER function from Excel
I am migrating a CVSS calculator I found online into Smartsheet, and it seems that if I can find a way to replace the POWER function from EXCEL, the rest of the formulas will fall in line. I need a solution to this formula : =IF($Scope1="Unchanged (U)", $[R_S]1 * $[I_Mul]1, $[R_S]1 * ($[I_Mul]1 - 0.029) - 3.25 * ($[I_Mul]1…
-
vlookup error
I am trying to write my first vlookup formula in Smartsheet and running into an error I can't resolve. This is the sheet that I am writing the formula in: This is the formula I've written: And this is the lookup_table am referencing: The Detail Status that is my search_value is in the first column of my lookup_table. My…
-
Formula help: Same criteria, same date, multiple rows
I feel like I'm missing something obvious here, but I'm struggling to come up with the correct formula to accomplish a task. Multiple attempts have come up empty. What I'd like to do is: When there are more than one instance of the Distribution column displaying 4 stars on the same day, I'd like the checkbox in the Status…
-
COUNTIFS Fomula
I created a COUNTIFS formula in Excel which would count the values "Expired" and "Overdue" and add the total's together. Excel Formula: =SUM((COUNTIFS(C:C, "CO-CURRIC-COI",J:J,"Expired")),(COUNTIFS(C:C, "CO-CURRIC-COI",J:J,"Overdue"))) When trying to emulate it in Smartsheet, I get a #unparseable error. Not sure what I am…
-
Problems returning a cell from an external sheet using "Contains"
=IF(CONTAINS([1]@row, {Shipping Range 4}), {Shipping Range 5}, "False") We are trying to use contains to search within a column that has multiple entries within cells to find a number and then return a reference from another column on the same row if a match is found. It works if we only want 'true' or 'false' returned.…
-
COUNTIFS - using multiple columns on CRITERION section
Hi all, I'm having some trouble with a COUNTIFS formula, and I'm hoping you might be able to help me. I am trying to highlight how many risks in a particular Risk Log have improved since mitigation plans have been put in place. The aim is for the formula to count risks that are not Closed, and in which values in the…
-
Countif formula; exclude completed status
Hello, I currently have the formula to count how many priority levels each team has but would like to exclude the completed items from the count. Is there an additional piece of the formula that I need to include?
