-
Index Match Else
Greeting All, I have created a simple index match that successfully matches state/province from my reference datasheet to pull in the corresponding district. I would love to indicate "N/A" for the instances when a state is blank but I cannot figure out the argument. =INDEX({District}, MATCH([State/Prov]@row, {Reference…
-
If And Formula for between dates
I'm trying to write a formula to return a "1" if the reference date falls between two dates. The highlighted Q1 should show a result of "1" based on the criteria further down the sheet as shown below. =IF(AND([Planned Date]@row >= $[Actual SIP Date]$177, [Planned Date]@row <= $[shipped On time]$177), "1", "") returns a…
-
#UNPARSEABLE error
Hi everyone. New to smartsheets and im having issues with my formula. Works fine on google sheets but dont know how to get it working on smartsheet. Please see an example:…
-
COUNTIF using 2 criteria
Hi, I'm trying to count the number of accounts using Experiencia with following criteria: Rounding Status is Operational AND EHR Status is NOT Operational. I'm getting an unparseable error with this formula: =COUNTIF([Experiencia Rounding Status]@row = "Operational", [EHR Integration Status]@row <> "Operational") Next, I…
-
Reports - calculating the gross margin rate
I am using Reports to bring data from multiple sheets in a project, but i need to calculate the project GMR (gross margin rate), how can I do this??
-
Countifs with blank "0" Value
Hi I am trying to put a formula together that will return blank if the value is zero based on 2 criterion.. example =COUNTIFS({Range 4}, ="Food & Beverage", {Range 3}, ="Unqualified" > 0 "") So basically if the value of unqualified is zero, I want the cell to be blank.. any tips?
-
#INCORRECT ARGUMENT error
Hi (again!) From other answers I've found here I'm guessing this is a parenthesis issue but I can't work out which ones. =COUNTIF({Clinical Trials : Accepted Range 1}, >=TODAY(), {Clinical Trials : Accepted Range 2}, HAS(@cell, "Blacktown")) Trying to sort current contracts (expiry date today or later) by research site ie:…
-
Pulling data dynamically from standard project folders
We found a template for multiple projects that uses a standard folder setup to keep Reports, Project Plan and Dashboards for each project within its own project folder... allowing us to have the separate lists of tasks and RFI's, rollups organized for each project rather than a forever-growing list of entries from old…
-
Formula to exclude expired dates
I'd like to count how many "current" contracts we have My reference sheet has a column for estimated end date, I just can't find the right formula to exclude dates that fall prior to todays date Thanks!
-
Formula to exclude expired years
Hi Hi, I'd like to count how many "current" contracts we have and also how many we have up coming close to expiry (say in the next 60 days) My reference sheet has a column for estimated end date, I just can't find the right formula to exclude dates that fall prior to the current date. And I have no idea how to write the…
