-
INDEX(COLLECT Returning #Invalid Value
Hello, I am getting #invalid value witht he following formula: =INDEX(COLLECT({Purchase Quantity}, {DepartmentConsolidation}, "112", {Consumables}, $[Product Description]@row, {Purchase Date}, >=[Week Start Date]@row, {Purchase Date}, <=[Week End Date]@row), 1) There is definitely a match on the sheet and if I remove "…
-
COUNTIFS using an OR with a column hosting 2 variables
Hello, Trying to set this formula to count 5 items using vlookup. The issue I'm having with that of the TYPE column hosting 2 distinct variables. I am not well versed but believe this needs an OR option. Curious if someone might know the best approach here. =COUNTIFS({HUB ERP - Function}, "Data", {HUB ERP - Status}, "In…
-
Count rows based on Date and Value criteria
Hi, I'm trying to count the rows in a sheet that have two criteria. I'm using the formula in a Sheet Summary field. The Date Initiated column cannot be blank and the Risk column is at High. Here is the formula I've tried with some variation: =COUNTIFS(Rating:Rating, "High", [Date Initiated]:[Date Initiated], ISDATE@row)…
-
Report text not wrapping
Hi, I have a report where the text is not wrapping even though it is wrapped in the underlying sheet. Actually, the report used to be text wrapped initially, but all of a sudden it's stopped wrapping for no apparent reason.
-
Identify duplicates (w/o DataMesh)
Hi team - I have an intake form that my workers are using that asks for their user email. In the source sheet are some entries that I prefilled out - including some employees and their respective email. Employees that were prefilled in were not to use the intake form. However, some of those employees decided to use the…
-
COUNTIFS with criteria & exclusion
Hello, I am looking for help with a formula to determine how many files are assigned to a specific person that are not closed. The column names are listed below: [File Manager] [File Status] I have a separate sheet for metrics that I am hoping to pull this data into and I have tried this fornula below: Note - the Label@row…
-
How to find 2 values from a multi-select column for reporting
I am trying to count how many times in a smartsheet 2 values show up in a multi-select column. example: i have a multi-select column with multiple options and I want to count how many times "Drainage" and "Roadway" are both selected. This can be done with the filter in the grid using the "has all of" options. This is the…
-
Count number of years between two dates
Hello I have read a few posts about calculating values between dates but I'm still having an issue. I am trying to calculate the number of contractual years between a Warranty Effective Date and the Warranty Expiration Date. The string I have is noted below but it’s obviously not working. Any thoughts?…
-
Can I nest more than one SUMIF
I am trying to create a SUMIF that will evaluate three options from drop down column and return a reduced percentage sum from within another column on the same sheet. Drop down Column - Affected Customer Impact Risk Options - Low Impact Risk (25%), Moderate Impact Risk (50%) or High Impact Risk (90%) Take the total amount…
-
Feature Suggestions for Reports
I use reports a lot in Smartsheet and we are constantly wishing for some features. 1) Ability to alter cell formatting in a report. Format edits would feed into the underlying sheet. This would allow users to color code or highlight cells for any number of use cases. 2) Ability to add a new row in a report and it would…
