-
CountIFs using multiple criteria
I want a count of the number of tasks that are complete. I don't want to include anything marked as not applicable or any headings (Hierarchy=Grandparent or parent), and it is counted as complete whether it completed by Medhost or by facility. =COUNTIFS([Not Applicable]:[Not Applicable], 0, Hierarchy:Hierarchy, "Child",…
-
How do I use percentage formulas to breakdown responses
I am currently tracking a project and have 4 columns that use the following values - pass, fail - see notes, n/a, not tested. How can I break this down % wise to see which line items have passed, failed, n/a or not tested per column? The goal would be to roll the information up to a report then up to a dashboard.
-
Help with a COUNTIFs Formula for a Date Range
Hello, I need a formula to count the number of times any date within July 2023 occurs within a set range of a column. This is what I have so far: =COUNTIFS([Date in Job]2:[Date in Job]76, >=DATE(2023, 07, 01),<=DATE(2023. 07, 31)) Thank you!
-
Invalid Data Type on an IF function with Index Collect
Hi All, I am trying to nest and index collect into an IF but I keep on getting Invalid Data Type. =IF(INDEX(COLLECT({Tracker - Range 5}, {Tracker - Range 2}, [Project Name]@row, {Tracker - Range 5}, "LATE"), 1), "LATE", IF(INDEX(COLLECT({Tracker - Range 5}, {Tracker - Range 2}, [Project Name]@row, {Tracker - Range 5}, "On…
-
List distinct values based on a criteria and remove blanks
I am making a metrics sheet that calculates what I need to show from a datasheet. The datasheet has columns for the Type (building type is either MOB or Hospital) and Departments (MOB has 12 departments and Hospital has 24 departments) along with a bunch of other columns that have data. In my formulas {CD - } is…
-
Adding multiple SUMIFS and return is $0 if one cell is blank
I'm trying to add multiple sumifs formulas together in one formula. However, not all of the sumifs will have an input. Example: Expense tracking, most of the time there will be only one charge code to utilize. There are times when multiple charge codes can be utilized and I'm only trying to extract the amount for a…
-
Need Help with Formula for Rolling Metrics
I am working on a 6 months rolling metrics calculation for a point system. A simple data involved will be as follows: 1) Date column 2) Points column (0-10) 3) Name column I need to calculate a 6 months rolling sum of points, for each person. I couldn't figure out how to calculate the sum of points for each person in 6…
-
How to use COUNTIFS where there are 3 conditions (columns to be referenced)
I'm trying to create a formula in a metric sheet that is referencing another sheet where data is held. I've been able to set up a COUNTIFS formula that references 2 columns so far, but my issue is that it doesn't seem to let me reference a third column using the "formula builder" as below: I'm trying to use COUNTIFS to…
-
Can I get a syntax check of the following Credit Amount Formula?
=IF((OR([Invoice 1 Status (Dropdown)]@row = "Credit Memo Required" AND ([TYPE of Credit] NOT(Rebill-Invoice Error))), =[Invoice 1 Amount]@row), (IF(OR([Invoice 2 Status (Dropdown)]@row = "Credit Memo Required" AND ([TYPE of Credit] NOT(Rebill-Invoice Error))), =[Invoice 2 Amount]@row), (IF(OR([Invoice 3 Status…
-
Assistance with JOIN/INDEX and COLLECT function?
Hey Community! What I'm looking to do: I have Part Number and Key Supplier as 2 different columns. When I put in multiple part numbers (the first column), I want to return all of the associated suppliers (column 2). Has anyone done this before?