-
Formula or report?
Need some guidance on a formula I'm trying to build. We have identical sheets gathering separate information and I need to gather that into a single formula to determine % of of sales. The formula I'm needing to build is: (discount 1-discount 2-discount 3)/(sales-financed) Discount 1, 2, 3, sales and financed are all on…
-
Need help with CountIF formula referencing two columns on another sheet with an OR statement
Current attempt: =COUNTIFS({Sheet Name - Standard}, CONTAINS("UM", @cell), {Sheet Name - Annual}, OR("yes", "add")) The gist is, another sheet, Sheet Name, has a column, Standard, and a column Annual, and for every Standard that is UM, we want to count if the Annual column contains "Yes" or "Add". There is a third option…
-
How to get the Delta between Estimated Duration and Actual Duration
Good Morning, We are trying to move our deployment plan for technical project implementations into Smartsheet. What I'm struggling with is getting the delta between estimated duration and actual duration to account for working days only. My start date/time, end date/time and actual finish date/time are all manual input.…
-
Broken Vlookup (No Match)
My vlookup is returning no match even though there is a match avaliable. But the reference sheet has an avaliable rate:
-
Warning -MONTH does not work as expected.
All, All our date ranges were setup using the 31st, which turns out to have been an issue. Even though the month is specified, the number of days (in this case 31) overrides the month entry and returned 31 days of results instead of just February. =COUNTIFS({ICR Tracker Review Range}, >=DATE(2023, 2, 1), {ICR Tracker…
-
SUMIFS: IFERROR Function to Ignore Empty Date Cells Not Working
Hello, I need to add an IFERROR function to the below formula, but cannot figure out what to input to make it work. I keep getting #INVALID DATA TYPE, #UNPARSEABLE, and #INCORRECT ARGUMENT SET errors. The goal is to return the total Amount for a particular Team (Retail in this instance) by week for only rows that have an…
-
How to set up dates to roll over effective FYE
Scenario 1: I need to calculate the total # of projects closed in the previous FY and display a running total of projects closed in the current FY. Right now, I'm changing the FY dates in the summary fields, and I would love to stop having to do this! FYE is 6/30, and my formulae are as follows. Current FY:…
-
RAG Status
Hi there, I can see this has been asked a couple of times, but I can't quite work out how to apply the formulas to my sheet. I have a RAG column, that I would like to tally up at the top of each section, and then an overall RAG status. The RAG status isn't currently related to the % complete, or the 'Project Status' (On…
-
#NO MATCH for COUNTIF formula
There is a column that is using a VLOOKUP formula to return an expression in my sheet. I'm trying to create a formula to count the number of Active Studys ("6. Being Studied") in this column, but I keep getting back, #NO MATCH, even though there ARE cells with that expression. I think this has something to do with it being…
-
Auto Generation of a Serial Number with Built in Logic Characters
I am wanting to see if anyone can assist in verifying an idea could be reality in smartsheet. I am wanting to create a auto generated serial number but the first 4 characters are based on user input. (Plant Code, Year, Month -Alphabet Character) then a 5 digit auto gen sequential serial number. It would have to reset after…