-
=COUNT(DISTINCT(COLLECT formula
Hi, I have a =COUNT(DISTINCT(COLLECT formula that is working in my sheet summary area. However when I try to recreate it in a metric sheet that pulls data from my sheet it only counts 1. Here is the formula that is working in my sheet summary. =COUNT(DISTINCT(COLLECT(Customer:Customer, [Who is your audience]:[Who is your…
-
Hi! Not great at formulas here :)
Trying to get the formula to populate tiering (None, T1,T2, or T3) based on the cabinet total using greater than, less than ranges. =IF([Column5]12 > 55 < 91, "T3", IF([Column5]12 > 91 < 121, "T2", IF([Column5]12 > 121, "T1", IF([Column5]12 > 55, "None")))). What am I messing up?
-
Check for duplicates and exclude blank cells
Hi. I am trying to write a formula that will check for duplicates in a column but skip the blanks. The normal formula I use is Countif([Column Name]:[Column Name], [Column Name]@row) this counts blanks. I have tried =IF(ISBLANK([Column Name]@row), 0, IF(COUNTIF([Column Name]:[Column Name], [AColumn Name]@row) > 1, 1)) I…
-
Set checkboxes for all children - multi parent heirarchy
I want to set a checkbox for all the children through multiple heirarchy based on the value in another column. If you look at the attached examples, if the parent value for active is N/A then that row and all it's children should be checked, including any parents with children within that heirarchy. I have three additional…
-
I am coming up with formula errors when trying to make the parent flag red if any children are red
The CHILDREN have, for example, =IF(AND([End Date]323<TODAY(),[% Complete]323<>"1"),1,0) and that works to turn flag red in all children but tried all kinds of COUNTIF formulas to also turn the parent red if any one or more of the CHILDREN are flagged red but all attempts are failing. Any ideas?
-
Formula IF and NOT Validation
I am trying to check a flag when two conditions are met: End Date is past Today's Date Status is NOT "Complete" I am sturggling with this for some reason and cant see where I missed something: =IF((TODAY() > End@row, NOT(Status@row = "Complete", 1,0))) Any pointers would be helpful.
-
Score formulas
We are having a sales contest this month. The teams (Houses) entered their numbers and we are ready to Identify the winners for 1st, 2nd and 3rd place. I was able to identify the 1st place score (Highest Score) using the MAX function and the name of the House (Top Team) with the JOIN function. BUT, I cannot identify the…
-
Formulas within conditional formatting?
I believe this must be possible, right? I have two column 2 my on hand amount and column 4 my par. I want the row to highlight red if the on hand amount is less than the par. Tried to use @row within the conditional formatting but it seems only be highlighting when the on hand amount is less than 1 not less than the par…
-
nesting properly?
Hi all and good afternoon, im working on a sheet that seems to be giving me an issue and I am aware its my lack of knowledge. Hopefully someone from the forums can give me some insight as to how to achieve my desired result. I have a cells contents with: =-SUMIFS({Transactions Range 1}, {Transactions Range 4}, ="Issue",…
-
7 Day Average/30 Day Average
I am trying to build a formula for a data set that I can calculate as a complete average, but I'd like the 7 day average and the 30 day average. Here's the formula that works: =AVG([% Successful]:[% Successful]) I know I will need to use AVERAGEIF, but can not get the date parts correct. Thanks for your help!