-
COUNTIFS with 2 values in the same column
Hello, I need to count status values which reside in the same column that are related to a specific track. I think I need to add OR( in the formula but am not sure how that is formatted. My current formula is below. =COUNTIFS({Status}, @cell = "Backlog", {Status}, @cell = "In Progress", {Track}, @cell = "Dashboard…
-
My COUNTIF formula is returning 0 instead of the correct decimal value. What am I doing wrong?
Hi, I am using this formula in a supporting sheet to calculate readiness for a task, but I keep getting zeros no matter what... =COUNTIF({Cross-site IHA Yes}, 1 / COUNTIF({Cross-Site IHA Ask 1}, 1)) Essentially this is saying, count all the "checked" boxes in column 1 and divide them by the number of boxes "checked" in…
-
Populate/Select a Contact (from a list) depending on the value selected in another cell
I have a problem I can't quite solve and I'm not sure if it's just not possible: I have a 'Responsible' column that is a list of Contacts. I have another column called 'Site' that is a drop down list. Depending on what 'Site' is selected I'd like the corresponding 'Responsible' to be populated. Example: If Site 'NYC' is…
-
IFERROR([formula], regurgitate or copy info from another cell in the same row)
=IFERROR(INDEX({Index NEW po amt}, MATCH([PO Number]@row, {PO number}, 0)), [Total Contract Amount]@row) The idea is I am cross referencing a PO amendment sheet on my main sheet. If there is a po amendment, I index and match based on the po number. If the index and match fails because there is no amendment listed on the…
-
Adding another IF Condition to my Current Formula
I'm looking to add this condition IF(Status@row <> "Complete", 0, 1) to my overall conditional formula below. Essentially I'm just adding the rule that if the status is complete than the value should be 1. I'm having issues embedding it in my existing formula, any advice? =IF(ISBLANK(Start@row), 0, IF(Start@row <= TODAY(),…
-
Help with Complex Hierarchy Functionality
Sorry the title of this question is not great.. Here's the abstract: I'm attempting a build for a large R&D department... My company built a similar system for internal use albeit in a slightly different way. We have two "Trackers" which are just sheets that are repositories for tons of information. One is an "event"…
-
Annual Rent Schedule/Forecast
Hi all! I want to create a yearly calendar for the 106 leases I manage showing the monthly rent for each property. My Master Lease sheet has columns for "Monthly Rent", "Rent Increase Date", and "Monthly Rent After Increase" which will be effective once the rent increase date is reached. If there are no renewal options…
-
How do I count the # of Flags and create a widget?
I would like to count the number of flags of each color under project type and put them into my metric sheet and reference them for a widget chart: So for instance, I want to count the number of yellow, green, blue and also red flags, with their prospective description and put them into the metric sheet: What formula do I…
-
Generate Status based off approved or declined
I need a status of approved when three people approve and denied when any of the three decline the request. Anything else between those I need pending such as just the admin director approving so far or even no one approving yet but the item was submitted. any thoughts? it goes admin director approval, then medical…
-
Moving rows
I have six sheets that are used for a process. So when it is completed by one department it goes to the next etc... The problem is that when you do the move workflow, it brings over the entire cell. Including the person that was dealing with it in the last department. Is there a way to stop that. My next thing would be to…