-
Duplicates, can I count the Original but ignore 2nd, 3rd etc
Hi I had a look but couldn't find the answer. I am trying to do simple reports and formula Counts, but trying to ignore any duplicates. Example below, I am only interested in counting the first time a name appears, so the count would equal 5 Names, ignoring where there is a second or third. Names Andrew Dave Phil Sarah…
-
Calculate days past due based on end date
I want to populate a column labeled "Days Overdue". I want to look at my column "% Complete" if "% Complete" is not equal to 100%, then get TODAY() date - "End DATE" and populate the value into "Days Overdue" column, else 0. In excel I can accomplish this with the following IF statement. =IF(% Complete<>100%,TODAY()-END…
-
Formula to add two columns together that meet the same criteria
Hello, Need some help with a formula, I am trying to add two columns that meet the same criteria from one sheet to total on another sheet. I have tried Sumif and sumifs formulas but they both comeback with errors Here is what I need: On sheet 1 I want to total columnA and columnB on sheet two if they meet (this) criteria.…
-
Hide Errors Until Cell is Populated
I'm calculating the variance between dates using the following formula. The problem is that an error is displayed if the Actual Start Date is not populated as show in the screenshot. Formula used in Variance Start Date: =NETWORKDAYS([Planned Start Date]3, [Actual Start Date]3) - 1 Is it possible to have the Variance Start…
-
Invalid Data Type using Find function
Hi All, I can't seem to find the error in my formula. I'm trying to use FIND() to locate a unique id # SE2 within a column on another spreadsheet. The column in the other sheet is a text column. =FIND("SE2", {**2019 Event Builder Range 1}) I keep getting back Invalid Data Type. I'm not sure what I'm doing wrong. Can anyone…
-
IF todays date is between start date and end date then check this box
I need to run an active projects report that collects data on in progress projects. I need to put the formula into the checklist column so that it checks it if todays date is between the start and end date.
-
Using One Checkbox to Check Another
We have a task sheet that uses 3 checkbox columns (Sent, Received or Done, N/A). We have a formula that counts the number of tasks and how many are completed based on the Received or Done column. We are trying to make it so if the N/A box is checked, it automatically checks of the coinciding Received or Done box. Please…
-
Index and match multiple sheets
Hi, I have an index and match formula set up for 1 sheet and it works great but how do I handle it if I want the index to look at data from multiple sheets to pull the correct information. My current formula is: =INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0)) I would like the index to look at the site number on…
-
N/A Column leads to #DIVIDE BY ZERO
I'm working on an onboarding tracker where I'm using an "N/A" checkbox in order not to count the modules that individuals aren't required to take. I'm using another checkbox to represent "Done" and the percentage complete - =COUNTIF(CHILDREN(Done16), 1) / COUNTIF(CHILDREN([N/A]16), 0) If an entire module is chosen as "N/A"…
-
Fighting an IF formula
I'm not sure what I am doing wrong. I am trying to get a response for "if the issue date is more than 10 days from the begin date AND the task is not 100%, show Late. Otherwise show 0. I will then be writing a report that shows only late. =IF(AND(NETWORKDAYS(Begin1, Issue1) > 10, "Late", "0", [%Complete]5 < 1), "Late",…