-
How do I do a COUNTIFS for a Department if the CHILD cell is either "Green" or Blank?
I'm trying to count each Active project that is below a Department that's Budget is either Green or Blank. In the example below, for Department 4, the Budget count should be 4 not 2. I'm using this formula =COUNTIFS(CHILDREN([Initiative Stage]@row), "Active", CHILDREN(Budget@row), "Green") but have had no luck with the…
-
Rolling 90 Day Formula
Hello, I need to write a formula that will check a box if a date is in the next 90 days, and uncheck if the date has passed. What I have so far is: =IF(Start@row <= TODAY(90), 1,0) So, if the Start date is less than or equal to 'Today' or the next 90 days, then check the box. If the Start date is not in the next 90, then…
-
What formula would work on finding the Top 50 largest values in a column/sheet.
In my sheet I need to find the top 50 performers and the total number (minutes) based on a month - so that if the Top #1 performer fell to #2 spot the next month it would update in my dashboard? I have 30 Teams (column=Team Name) with 25 ppl /team (column=Team Member), each month each person from that team enters their…
-
editing across sheets for text consistency
Hi all, I'm not even sure what to ask for, other than help. I have three key sheets that contain many of the same fields. I've (finally) figured out the Index Match formula to pull matching field in from another sheet (there was much rejoicing!) but now am needing to be able to edit the info. Is there a way to do that…
-
Workflow to change cell value / lock row not working when lookup between sheets shows a result
Hi. Wondering if anyone is able to assist here. We have 2 sheets (DA and POC). Both have individual unique reference numbers. When I create a new row in DA (unique ID = DA0001) there is a field in DA0001 where you can add a unique ID from POC (example POC0005). So the row would look like: DA Ref DA Name POC Ref D0001 TEST…
-
Health Ball Color Based on progress
Hello I have =IF(OR([COS 2022 ACT - YTD]@row > 0.05 + [COS 2021 Baseline]@row, "Green", IF([COS 2022 ACT - YTD]@row > 0.1 + [COS 2021 Baseline]@row, "Yellow", IF([COS 2022 ACT - YTD]@row > 0.15 + [COS 2021 Baseline]@row, "Red")))) COS 2021 Baseline COS 2022 Target COS 2022 ACT - YTD Can you please help me with this: over…
-
matricial multricriterias sum
Dear Community, I exhausted all my ideas. I'm using smartsheet to follow a project. Based on the "start / end date" and the "assigned to" field I'm trying to sum the availability of the ressources. To do so I'm referencing another sheet in which I have my team planning (row = date and column = people), if they are in…
-
Hi, I am trying to workout a formula which can help me pick a specific category and count all that c
Hi, I am trying to workout a formula which can help me pick a specific category and count all that started in a specific month. For example: I want to count How many Indirect Product and Services activities started in April. I am using the below formula which is giving me 0 value =COUNTIFS({Dem Pep Range 8}, "Indirect…
-
Count Help, Don't Know Where To Start
Hi Everyone, I would like to do a project status summary. Each parent is a school site, and the children are the projects that need completion. In the drop-down menus next to the projects I change the project status to complete when done. I'd like to create a list of all the school sites included in this project that shows…
-
Formula - Adding a Column
Today, I created the following formula in a cell =IF(AND([Roommate in Room?]@row = "Yes", [Requested Bed Type]@row = "Double"), ".5", IF(AND([Roommate in Room?]@row = "No", [Requested Bed Type]@row = "Double"), 1, 0)) As you can see one of the variables returns a .5. The formula works great until I go to my sheet summary…
