-
COUNTIF with multiple criterias
I have a formula in my sheet to count the number of unchecked boxes in a column. I'd like to set a condition so that it only counts if the box is unchecked and there is a name in the "facilitator" column (Contact email format). Alternatively, if it's easier, it could count IF there is a date in the automatically generated…
-
How do I get a sum for each location (Room 600-605) excluding expired materials?
In this first image the "Complete Count QTY" column sums the materials excluding expired materials. This works perfectly. In this image you can see my attempt at getting a sum for Room 605 in the "Location Specific QTY" column excluding expired materials. This is where I have a problem. Ideally this would read 4 in the…
-
Formula to Flag or Not Flag
Goal: Flag when the due date is in the next 7 days No flag when the due date is BLANK or more than 7 days in the future Two formulas that seem to work separately: =IF(Due Date2 <= (TODAY() + 7), 1, 0) =IF(ISBLANK(Due Date2), 0) Anyone know how I can combine these formulas to make both work?
-
Issue with formula giving inconsistent answers
I am trying to identify FAILS and PASSES the question "Within how many days is your earliest routine appt" (dropdown with selection of days to chose from). Many of the states have different requirements to determine pass and fail. Therefore, the formula is somewhat complex depending on state and the requirement. Above is…
-
INVALID OPERATION error while building a IF/AND formula
I am trying to build a formula that identifies project stages based on where we are date-wise in the project. I started building an embedded IF/AND statement that was looking at each project stage date range and if TODAY's date was in that range it would identify that as the stage. I got an INVALID OPERATION error so…
-
using index match to automatically pull newly created cell data from form
I have been successful at pulling previously populated cell data from sheet A which is auto-populated by Form A to populate specific cells in sheet B; however, I have not been able to find a way to automatically pull newly populated data coming in from Form A or a recurring/cyclical basis (e.g. anytime a new form entry is…
-
CountIf and Multi Select columns
I am trying to count the number of instances a value appears in a multi select drop down column, but I keep getting an unparseable error. After searching the existing help forum I have tried both the CONTAINS and the HAS functions as follows, but receive this error for both of them. The formula's I have tried are:…
-
Join Collect Header?
I am trying to figure out how to consolidate a group of companies that use a specific application. Each application has its own row. Each company has its own column and a "Y" is added to the field where they intersect to indicate that the application is used by the company. I would like to combine a list of all of the…
-
Formula if multiple choices
I have a Countifs formula that I can get to work if a single item is selected from the dropdown but not if both options are selected. I want to count: 1) Who's affected is Internal (works): =COUNTIFS({ProjectStatus}, "Open", {WhoAffected}, "Internal", {Live}, "Live Input") 2) Who's affected is External (works):…
-
INDEX/MATCH for Mirroring Status Column in Another Sheet
Hello! This may not be right formula, but if a user shared a sheet with me which contains a project codes column and a status column (complete/incomplete), and I use the same project codes, can I do a formula which pulls in their status into my sheet? Something like - If (External Referenced Sheet / Column A) matches…