-
Sheet Summary Formula Error due to Formula in Sheet
I'm using Sheet Summaries to total up information in a large sheet. This sheet also uses formulas to pull information from other sheets. I receive a #NO MATCH error when attempting to use a COUNTIF formula in the Sheet Summary on fields that are being pulled from other sheets via formulas. Is there any way around this…
-
Harvey Ball changes based on completion and dates
Hi, once again I feel I'm so close with this one, as I can get Blue and Green to work as expected, but Red and Yellow don't appear to want to play ball (see what I did there?). =IF([% Complete]30 < 1, "Green", IF(AND([End Date]30 < TODAY() +10, [% Complete]30 < 1), "Yellow", IF(AND([End Date]30 > TODAY() + 10, [%…
-
Issue with using formula to count rows in sheet that do not contain certain text
=COUNTIFS({All Deals Input (Master) Range 1}, $[Primary Column]@row, {All Deals Input (Master) Range 2}, [IDR In Process]$2, NOT(CONTAINS(OR("Kill but Follow", "On Hold"))) + COUNTIFS({All Deals Input (Master) Range 3}, $[Primary Column]@row, {All Deals Input (Master) Range 2}, [IDR In Process]$2, NOT(CONTAINS(OR("Kill but…
-
Trying to use index collect, vlookup and index to reference two columns on two sheets
I am trying to write a formula based on two sheets. One sheet, Team Members, that has the following columns: Column 1 - Region Column 2 - Industry Column 3 - Contact The other sheet has many columns, but it has matching columns of the same Column 3 - Region Column 4 - Industry Column 10 - Contact I've tried a bunch of…
-
Conditional Formatting is not working properly.
I'm using the formula =IF(ISBLANK([Citizen Doc#]@row), "", IF(TODAY() > [Citizen Exp Date]@row, "Expired", "")) that will show the word "Expired" if the conditions are met. The correct records show the word "Expired". My conditional formatting is to show a red background with white text. It properly shows this in the…
-
Help identifying duplicates using two columns but excluding entries.
I would like to locate duplicates in my sheet using the name column and email column. Just starting with the name column gets me nowhere. I keep getting #UNPARSEABLE. I am using =IF(COUNTIF(Client Name:Client Name, Client Name@row) > 1, 1) in a separate column. Eventually I would like to include the email column which has…
-
formula question
Hi I am struggling with a formula to calculate a number of fields and looking for some help. I want to calculate the number of both estimated and actual hrs for each month for each client, I have tried the below, but I cannot see where I am going wrong, I have tried COUNTIFS also and cant get it to work.…
-
How can I document the history of Open/Closed Items on a separate sheet.
Summary: I have a sheet of data that pulls a report of items that are open. Each row on the master data sheet hold equipment data, then holds a multitude of columns split into (3) stages. (See sheet example below) The column that lists the open items for each piece of equipment is set up with the following formula to…
-
COUNTIFS formula not functioning how I expect
Hello, I am expecting this formula to count the number of cells in the "FND Prepare" column that have a certain name in them, where the cell on the same row in "Prepare Status" is not green. =COUNTIFS([FND Prepare]:[FND Prepare], HAS(@cell, "NAME"), [Prepared Status]:[Prepared Status], (@cell <> "Green")) Does someone have…
-
"Invalid Value" result from Index-Collect formula
Hi folks! I'm having difficulty with an Index-Collect formula resulting in "Invalid Value" error. I've simplified the formula to the absolute basics to try to isolate the problem. Here is my current formula: =INDEX(COLLECT({YTD-ActualsByMonth_Video-PIDs: Contractor Cash}, {YTD-ActualsByMonth_Video-PIDs: PID}, [PID…