-
Sum IF - Adding data after conditions are met
Hello, In the attached image, I want to only SUM up rows with "DONE" in the Status Column. I want to sum up by month so I want to add the values in Story Points column by month. Example - Add up all rows with "DONE" status and total the points in January, February etc... Thank you! Amy
-
What is the issue with my formula?
=IF(AND(% Complete]3 < 1,[End Date]3 < TODAY()), "On Time", IF(AND([% Complete]3 < 1,[End Date]3 = TODAY()), "Due", IF(AND([% Complete]3 < 1,[End Date]3 > TODAY()), "Past Due","Complete"))) If i validate each part of the formula separately it returns my intended value however when I try to embed using IF AND, it is always…
-
INDEX MATCH to return last matching row value
Good day, I would like to return the last matching row using INDEX MATCH for unsorted data. In the example below, I would like to return the last Date Log - 2020/05/26 for NODE B? To return the first Date Log, I use the following formula that works: =INDEX([Date Log]1:[Date Log]6; MATCH([Search value]1; NODE1:NODE6; 0))…
-
Adding a Second OR to Formula
Smartsheet Community: I have to add a second OR Statements to to the Formula but can not put my finger on what I am doing wrong. Current Formula: =COUNTIFS({UAT Issue Tracking Sheet Range 4}, "UAT - Phase 1", {UAT Issue Tracking Sheet Range 3}, "Highest", {UAT Issue Tracking Sheet Range 5}, OR(@cell = "New", @cell = "In…
-
Alerts based on lookup
I have a column in a sheet which is a Multi Select Dropdown with a number of roles listed. I want to set up alerts to individuals, sending them the rows in which their role is mentioned. The email addresses relating to each role are held in a separate sheet. Is there any way I can use lookup to send the alert to the…
-
Countif Function where criterion is content of a cell
Hi All, I need some help with a countifs functions I want the formula to provide me with the count of the cells that contain a specific text. Is there a way to point directly to the cell that contains that text rather than spell it out ? For example =COUNTIF([Column A]:[Column A], "Text") This works fine, but I would want…
-
Use Wildcards in an INDEX MATCH formula
Hi everyone, I need to use INDEX MATCH formula to get a DS# with a CNDT# in another report. The easy formula would be: =INDEX({DS Report Range 2}, MATCH([CNDT Number]1, {DS Report Range 3}, 0)), "") but the thing is that sometimes there are more than one CNDT#'s in a single cell. Since INDEX MATCH looks for the exact match…
-
Function AVERAGE CHILDREN excluding some "childen"
I have a problem with formula "Average Children". In my example below, when there is no cost so % is 0%, it's included in the average of children but should not. In this case, on the third row "3%" should be 23% and in the first row "1%" should be 23%. Is it possible in a formula to say "Average children except if children…
-
Verifying Created by Column with Manager Email Column
I'm trying to show verification for a contact email address to match the "Created by" field generated by Smartsheet to ensure an entry/request is valid/accurate. Can someone help me out with this? Essentially flagging people with inconsistent data. Thanks so much.
-
COUNTIFS
Hello, Hope you can help me. How do you use Countifs formula using the table below? 1 ) I need the count per month, per name of the person who bought it. There's only two names, Mary and Joseph. I have 4 individual sheets per raw materials. Date month column is listed mm/dd/yy Sheet columns look like this below: Date Rcvd…