-
How to Setup COUNT/COUNTIFS formulas
I manage the educational benefits at my job and am trying to find totals of how many dependents, drivers, and office employees participate in the benefit. This is how I've been setting up my formulas. Yet when we cross reference with Excel the total number is off. =COUNT(DISTINCT(COLLECT([Employee ID/Driver Code]:[Employee…
-
COUNTIFS keep coming back as zero
Hello all, I'm trying to do a count of all projects in Green (later for Yellow and Red) status across 4 different team sheets and this is the formula that I came up with : =COUNTIFS({Team B Range 1}, FIND("Green", @cell) > 0, {Team F Range 1}, FIND("Green", @cell) > 0, {Team G Range 1}, FIND("Green", @cell) > 0, {Team V…
-
Smartsheets Formula Error Code
Hello Smartsheets Community, I have this code and it is resulting in an "Invalid Data Type" error. The code should display the number of rows which the following column criteria applies: Column 1 Look for the letter "N", column 2 look for the number "1", and column 8 looking to see if it is in august. My formula is below:…
-
What am I doing wrong with my formula
I am trying to add the original contract column and approved change order column to give the total to the corresponding row in the revised contract column as well as keep as total at the bottom as you can see with the original contract and approved change orders. Can someone tell me why it is coming up unparseable?
-
Nested IF Formula
I would like the IF statement to return a specified value if based upon date ranges. For example, if a date falls within a range: 09/01/23 - 11/30/23 should return the value "Q1" 12/01/23 - 02/29/24 should return the value "Q2" 03/01/24 - 05/31/24 should return the value "Q3" 06/01/24 - 08/31/24 should return the value…
-
Invalid Value Error in some rows of a Formula Column
I have an inventory transaction sheet that I have been working on for a little bit now. Looking at my Stock Alerts column again, it looks like there is an issue. The purpose of this column is to alert if the quantity requested is greater than or equal to the current quantity. If there is an error, then flag that the item…
-
How to count the latest status on a record
Hello! I have a sheet that's feeding on an automation from another sheet and it is set to copy a row whenever a status changes on a case. Now I'm working on a series of monthly dashboards and I want to show the count of cases that where in each one of the status before each month ended. This is my sheet that's feeding from…
-
How do you calculate a total from a list from multiple sheets?
I have (3) sheets that I have a long list of services and the cost associated with them. Which I can combine in (1) sheet if needed... On what I will refer to as my destination sheet, I have aggregated the services selected by a client and I need a formula that will reference the cell that has all of the services listed in…
-
How do I solve this UNPARSEABLE error for a cross-sheet SUMIFS formula?
I'm attempting to sum a column in another sheet based on matching the product name, the month, and the vendor values in the current sheet but I can't seem to resolve the UNPARSEABLE error. What am I missing? =SUMIFS({Quantity}, {Product}, Product$1, {Month}, Month$1, {Vendor}, Vendor$1)
-
Helper column for date conversion
I have a column in a sheet that is set to pull the date and time a work order was created. I am trying to add a helper column to convert this column to date only but it doesn't seem to be pulling in the correct year. Can anyone help me with my formula? =DATE(VALUE("20" + MID([Date WO Created]@row, FIND("/", [Date WO…
