-
Nested Criteria on COLLECT
I'm trying to get an income by multiplying quantity with Price. Price is located on another sheet so I have to find it based on type, route, and active date. Here's the formula that I have: =IFERROR([Economy QTY]@row * (INDEX(COLLECT({Price Range}; {Type Range}; "Economy"; {Route Range}; CONTAINS(Route@row; @cell); {Active…
-
Best formula for a complicated desired result?
Hello, I have tried many different formulas and even tried combining formulas but can't seem to get one that works in a way for syntax to be used as a column formula. Any help would be appreciated, and I thank anyone in advance for tackling this. The desire result is a formula that can be converted into a column formula…
-
I would like to make an IF index collect
I am working on creating an IF(ISBLANK({cross sheet reference of column}), " ", Index(Collect({ NAME},{cross sheet Helper column}, helpercolumn@row,{cross sheet status}, "Status, {CrossSheetDate}, Contains("/",{CrossSheetDate}),1) I get an error for the contains part and the formula works without the date, but I am trying…
-
How can I get my formula to return a phrase instead of #INVALID VALUE
I have a sheet that lists job openings. There is a column formula that assigns a "status" for each job by searching another sheet of applications. For example, if a job has 3 associates applications, and their statuses are "New" "Interviewing" and "Offer" it assigns that job a status of 'offer" because that is the furthest…
-
On an Index Match, why is it returning a match when the match criteria row is blank?
Hi, I am trying to get a a person's region included in a sheet by matching their name in the Staff Column to the source sheet. However, when the row in the Staff Column is blank, I am getting a return from the first line of the source sheet. Is there a way to get the return to be blank if there is nothing to match in the…
-
COUNTIF formula counting specific criteria under a PARENT row
Hi All, I have a shipping tracker I created for six different stages we're doing for a project. I need to create a graphical representation of the different statuses for each stage and type. The formula would read: Count of "delivered" for "POM to IHA" under "WW01, as one example. A separate formula would be Count of "In…
-
Count how many within a year
Hi All, I have column title named 'Type of Request' and you can click multiple answers, so a cell can contain 1 or maybe 4 answers for example... . I have another column called 'Date Created' which is the date of the request. What i am trying to do is a formula that will tell me how many requests we have had within the…
-
Totals and Sub Totals
Sorry if this is an obvious question but I am new to Smartsheet and I am trying to get my head around Totals and Sub Totals. In our Project Plans we have 3 columns relating to Effort: Planned Effort Time Spent Effort Remaining What I need is to be able to get a Total for each column in say Row 1 and Sub Totals for each…
-
What formula would I use? IF/MATCH
I am trying to put a formula together to say if there is a date, list it here (On-Site Training), if it says no match, then don't do anything.
-
Can a "checkbox" cell be referenced in a formula?
Can a formula contain a reference to a checkbox cell and then return a result based on if that box is checked or unchecked?
