-
Count the number of selections in multiple drop down lists in one cell.
I am trying to count the number of selections (not caring what they select) in multiple drop down lists on multiple columns. Any example: If a person where to select 3 items from the drop down list in A2, and 4 items from the drop down list in B2, and 1 item from the drop down list in C2, I would want D2 to show 8. I am…
-
Why am I receiving #INVALID OPERATION error in some of my sheets
I have multiple sheets with the same columns (Health, Task Name, Start, Finish). Note that the Health column is a symbol (red, yellow, green, blue). In my summary sheet, I am using the following Index Match formula to search for Task A in Task Name column and spit out Health. =INDEX(Health:Health, MATCH("Task A", [Task…
-
Using COUNTIF in Summary
Hello I'm looking to create a dashboard. I've got a column in a sheet that has 'Not Started', 'In Progress', 'Complete' as options. I could do with a report that shows 'Not Started' = x and 'In Progress' = y etc. How do I go about this? Ideally I'd like to put Countif into the Summary rather than update it manually as the…
-
AVG % that includes blank children rows
I am turning off the % complete in the project sheet setup due to a milestone (Parent) section that requires manual input of the % complete. Therefore, I need to enter formulas in the % Complete column parent to calculate the % complete for the children but it needs to include the tasks that are blank. AVG(CHILDREN()) does…
-
How to get Max children in parent row with condition based on partner column
I am trying to get a formula in the parent rows for this sheet (blue rows) in column 2 that returns the max value of children in the 2nd column for that parent but only if that value also corresponds to the max value of parent row in the first column. So in the screenshot below, for Title 13 row, the results are currently…
-
UNPARSEABLE Error
Hi. I am trying to pull from my contracts management sheet, the number of "Executed" contracts that have a "Y". For background info, the "Y" denotes the last and active executed contract in a series. I don't want it to count all multiple executed contracts/amendments just the most recent one. =COUNTIFS({Sheet - Contract…
-
Can a formula not include references to other cells whose values are based on formulas?
A formula is returning "No Match" in the example pictured below. The formula seems correct based on another sheet it is working in. My only guess is that a formula cannot reference two other cells that each have values based on formulas themselves. Any thoughts are appreciated.
-
Struggling with Lookup formula
Hello everyone, I am struggling with a formula and I would really appreciate your help. Here is my idea: Sheet 1 is a Trainings Catalog, where I have two columns: "Mandatory" and "Option". I complete these columns with the job positions concerned. Sheet 2 is a database which contains employee information, such as the job…
-
Numerical "Priority" Method
Hello, I am in need of a method for assigning priority of a submitted row 1-10. If 1 is selected, the previous 1 needs to become a 2, the 2 becomes a 3, etc. The column needs to recalculate when a row is complete and the 'priority' column becomes blank. I don't think this would be possible without using many columns to…
-
Portfolio Metrics Formula
Hi everyone, I'm trying to calculate from two different columns on a portfolio summary and can't quite figure out the correct formula. I've gone through and tried many of the suggestions from here without any luck. What I'm trying to accomplish: Count how many (blue) items are in "Health" column, but only if "Key Contact"…
