-
Subtracting checked rows from a total
I'm trying to figure out how to create a formula that would subtract checked rows from a total. For example in the photo I have the 4 items on the right summed in the upper left to $16,000. Every time i check a box and get a strikethrough, I'd like the total to adjust to the remaining items. Thanks!
-
Adding a condition to recognize if the external reference cell has any data or no data at all
I have a metrics sheet that pulls data from another sheet. I want the formula to mirror the way the Filter feature works to say "if this cell has any data whatsoever in it (in this case any name) then count it. if the cell has no data, then don't count it." This is how I have started my formula: =COUNTIFS({Post Closing…
-
Identifying Duplicates
I am working on a sheet that contains IP addresses. When reports are added to this sheet, I am currently going through the data and marking any duplicates. I want to find a way to either use conditional formatting to highlight the duplicates or any method that is available. Is this a possibility without inputting the value…
-
SUMIFS in date range using CHILDREN only
Hi, I'm trying to do what I thought was a fairly basic thing but I keep getting tripped up! I have an expenditure spreadsheet with different categories divided into Parent -> Child -> Grandchild. I want to sum a column [Actual EX VAT] dependent on it happening within a date range in the [Order Date] column. BUT to only…
-
Autonumbering outdent and indent separately
My outdents are initiatives and my indents and milestones and would like to number my outdents INIT001 and so on, and my indents to be numbered as MILE0001 and so on...though I dont seem to be able to do this. I've tried having separate columns for this also one for Initiative Number and one for Milestone Number but the…
-
new to Smartsheet and struggling with If / And Formula
Hi. I'm fairly new user to Smartsheet and struggling with formulas i could create in excel. I have a sheet that will generate a RAG status based on the combined scores of two columns (likelihood and impact) but cannot get my If /And formula to work at all. I am user a separate helper sheet to generate the results. The…
-
Can I exclude certain criteria from my formula?
Hi there, I have 3 main columns I am working with. Column A = multi select dropdown (words/text) Column B = A Date that is manually entered Column C = A formula that counts 5 days from the date in Column B: =IFERROR(WORKDAY([Column B]@row, 5, Holidays:Holidays), " ") This formula is working well. It counts 5 days forward,…
-
How to create a SUMIF with ISNUMBER
I am trying to add three separate columns together, but sometimes, one column will have text in it instead of a number. Is there a way to get the formula to exclude anything that's not a number? My brain isn't functioning high enough to come up with something that works lol. This is my current formula: =IFERROR(Rate@row +…
-
Summary no longer summarizing
Hello you wonderfully helpful community, I have a report that is showing me if rows were cancelled or released. When I first set up the report about 5 weeks ago, The summary would show me a count of checked boxes. For instance, 7 cancelled and 32 released. The report now just shows a count of rows regardless of checked…
-
Converting an Excel IFS statement to SmartSheets
Having trouble understanding why this keeps being unparseable in SmartSheets. Original Excel formula: =@IFS([@Total]>=20, "Prioritize These",AND([@Total]<20, [@Total]>10),"Small Quick Wins",AND([@Total]<=10,[@Total]> 0), "Major Projects") My SmartSheet attempt: =IF(Table22[[#This Row],[Total]]>=20, "Prioritize These"),…