-
countifs not including 2 status
I am trying to count if Lilly is the controller, and the status is not cleared or delivered. Having trouble with the formula. =COUNTIFS(CONTROLLER:CONTROLLER, "Lilly Spanbauer", {status}, <>"Cleared", {status}, <>"delivered")
-
Fill in Date Columns with Letter to represent a Hotel
Hey! I am working to convert a google doc and its formulas to smartsheet. Essentially, I am using smartsheet as a rooming list that lists people, their hotel, and their check-in and out dates. There is a column for every date so we can add up the totals of everyone at each hotel on a given night. What I am hoping to do is…
-
INVALID DATA TYPE error when using MONTH()
Hi, I'm working on a formula that will use a date to reference a value in another sheet: The sheet that will be referenced looks like this: I'm planning on using INDEX and MATCH to get a date value from a row in the first sheet to return a percentage value (based on the month that row occurs in) from this sheet. The…
-
How do I populate a destination sheet with data from a source sheet based on a checkbox from source.
I need to bring data from column 6 of source sheet based on a checkbox being checked in column 1 in same sources sheet. Is this possible?
-
Filter a Join Collect formula from another sheet.
I have sheet, "Component Inventory", that I'm using to summarize the quantity in stock of a part number in a sheet called, "Inventory Log". "Component Inventory" has a QTY ON HAND column that is populated by a SUMIF formula getting its data from "Inventory Log" sheet. The "Inventory Log" sheet has a column INVENTORY…
-
Index, Collect multiple criteria
I am trying to index, collect the approver name from a matrix of approvers by business role. The formula works if only one business role approval type is displayed, however I need to be able to assign an approval/ sign-off name for all business role approval types selected =IFERROR(INDEX(COLLECT({BRD approver name},…
-
SUM by Month and a task type
I want to create a summary sheet and show totals by month by a certain column Metrics Report Main Smartsheet =SUMIFS({CopyWritten}, $[Primary Column]@row, {Monthcompleted}, Jun$1) = 0, "", SUMIFS({CopyWritten}, ${Primary Column]@row, {Monthcompleted}, Jun$1)), "") The error I'm getting is #unparseable Then I want to break…
-
Health based on check box
Hi Community, I want to automate my health column, based on whether several checkboxes (milestones) are ticked or not. Health column is indicated in heart symbols ("Empty" - "Five"). That's what I have right now, but causing an error: =IF([Infra Team informed to remove legacy hardware]@row = 1, "One", IF(AND([Infra Team…
-
Count a row if the date AND Member Number both do not match another row
I am trying to create a formula where it creates a 1 in a column where the date AND the Member Number both in one row do not match the date AND the Member Number in another row. For example, in this row, the date is 05/20/21 and the Member Number is 987654. If another row also had the date as 05/20/21 and the Member Number…
-
If/Then Statement to Return a specific date
Hello, I am new to Smartsheet and though I feel like this should be a simple formula, I am stumped. I am hoping to create an automation that would calculate a due date from a date received based on the value selected from a drop down list. So if I choose option a from a drop down list (Permit Type), then add 30 days to the…