-
SUMIFS with date and category referencing another sheet
Hi - I have a cashflow chart that I want to pull expenses into by category and month. The expenses are located on another sheet with a dropdown list that is the same on the cashflow chart. I have tried this formula but it is not returning the results - just $0.00 =SUMIFS({Expenses Amount}, {Expenses Category},…
-
Network Days + If statements
Hello, I'm trying to build a formula that will show the network days between a [Start Date] and an [Completion Date], but also 1) if [Start Date] is blank, then return a blank instead of an #Invalid Data Type error; and also 2) if [Completion Date] is Blank, the formula subtracts the [Start Date] from Today(), so that we…
-
How to collect multiple Successor values?
Here's a link to my initial post which helped get me this far. RECAP: I use a combination of a Successor formula & Join/Collect in an attempt to report for my team the next tasks and assignee(s) in our waterfall workflow. I am able to successfully return the next assignee only when there is a single successor. I need to…
-
Help with addition to formula
Hi, i need to make an addition to the below formula please. I need to add if column type is issue and impact score is 4 - gray, 3 - red, 2, amber and 1 green =IF(AND(Type@row = "Risk", ([Impact Score]@row * [Likelihood Score]@row ) >= 20), "Gray", IF(AND(Type@row = "Risk", ([Impact Score]@row * [Likelihood Score]@row ) >=…
-
How to Have Children Rows Updated based on Data from Parent Row
I am setting up a sheet which will have many parent rows with many children connected to them. There is a column called "Escalated to AM?" which is a drop-down with the option to select "Escalated". There is another column called "Escalated?", which is a checkbox column. I want to add a formula to the "Escalated?" checkbox…
-
Counting with multiple criteria
I dont know what im doing wrong here, im trying to count anything with Mentoring and if Q1 is found =COUNTIFS({RelatedGoalWoW}, HAS(@cell , "Mentoring")) + COUNTIFS({Quarter}, HAS(@cell , "Q1"))
-
INVALID OPERATION ERROR - HELP NEEDED
Thank you in advance for your help. I actually worked with Smaartsheet's AI Help to generate and work through the formula. I have checked it twice and still get the same error message. Here is the formula: =IF(AND([Tester ID]@row = {DE2ET Tester ID}, [Scenario ID]@row = {DE2ET Scenario ID},…
-
How can I use Conditional Formatting for date in the past or future when a formula created the date
Hi Everyone, Thank you for your time in reading this. How can I use Conditional Formatting for date in the past or future when a formula created the date? The black columns are different dates generated. The 2 yellow columns are then selecting the relevant date based on different criteria. The white columns are me trying…
-
Help with INDEX/DISTINCT Not Working!!
I have the following formula: =IFERROR(IF(Auto@row = 0, "COMPANY NAME", INDEX(DISTINCT({Company Name}), [Row ID]@row )), "") Problem is - it has stopped working. It pulled out the first 115 vendors out of 425 items, and then stopped. Is there a maximum number of distinct items it will pull into a list? I do not see any…
-
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…