-
How can I write a COUNTIFs using a dropdown in one argument and not another?
I have 2 columns that I am trying to do a sheet summary on and can't figure best way to write. one column is a free text and the other column is a drop down list with several months. I need to get the counts of "Leaders" who select certain months (Oct, Nov, Dec, etc). Can this be done using CONTAINS ? =COUNTIFS($[Leader or…
-
I need help calculating the sum of one column based on a date in another column
OK, so I'm trying to calculate values (in one column) based on the Month (in another column). This is what I have tried: =SUMIF({1 - Sales Pipeline: Holly Greenhalgh Range 2}, MONTH(@cell) = MONTH(TODAY(), {Sheet - Sales Pipeline Range 3})) However, it returns 0. I'm referencing another sheet if that makes a difference.…
-
SUMIFS Formula
Hi I am trying to create a formula that will sum the No. of Minutes Taken to Complete Job based on the Job Type. This is what I'm using currently: However, its' bringing back zero when you can clearly see a CD Extraction Job Type in row two. I'm assuming it's because it is a drop down but, there will only ever be one…
-
Count Date + N/A
I have a columns "LAN M Actual Complete" and another column "2nd Touch Indicator" I need total Site complete: count if "LAN M Actual Complete" has a date and "N/A" in the "2nd Touch Indicator column" Dont' count if "LAN M Actual Complete" has a date or no date and "2nd Touch Indicator" is blank or something else besides…
-
If/Or Question
I would like RYGB column to; default ='Green' if today>[Proposed Start Date] = 'Red' if today => ([Proposed Start Date]-7) = 'Yellow' if [Actual Start] is not blank = 'Green' I am able to default to 'Green'. I have formula for 'Red'. I'm stuck when I try to add to my formula. The below is returning 'unparseable'. I've…
-
My COUNTIF formula is returning 0 instead of the correct decimal value. What am I doing wrong?
Hi, I am using this formula in a supporting sheet to calculate readiness for a task, but I keep getting zeros no matter what... =COUNTIF({Cross-site IHA Yes}, 1 / COUNTIF({Cross-Site IHA Ask 1}, 1)) Essentially this is saying, count all the "checked" boxes in column 1 and divide them by the number of boxes "checked" in…
-
Adding another IF Condition to my Current Formula
I'm looking to add this condition IF(Status@row <> "Complete", 0, 1) to my overall conditional formula below. Essentially I'm just adding the rule that if the status is complete than the value should be 1. I'm having issues embedding it in my existing formula, any advice? =IF(ISBLANK(Start@row), 0, IF(Start@row <= TODAY(),…
-
Whats wrong with this formula?
I'm using this in a metric sheet to count the term in a sheet that has multiple entries per row from a drop down list. It currently produces the error message #INVALID OPERATION. =COUNTIF({PH Roadmap Intake sheet Range 1}, CONTAINS([Primary Column]@row)) I was attempting to copy the one @Paul Newcome provided on 9/8/21 but…
-
Combine if ISBLANK in an existing formula that pulls percent complete based on duration
I have the formula that pull the percentage complete based on the duration between 2 dates =IF([Start Date]@row <= TODAY(), NETWORKDAYS([Start Date]@row, MIN(TODAY(), [End Date]@row)) / Duration@row, 0) What I'm trying to do is add the formula below to make a combined formula. Which should make the number zero if there is…
-
I need help with an IF(AND..IF(AND.. formula that I'm getting an #UNPARSEABLE error on.
I'm trying to replicate a risk rating matrix (image included) by taking the data in 2 cells (Risk Likelihood & Risk Impact) to auto-populate a third cell (Risk Rating). Below is the formula that I am using. =IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Insignificant"), " Low", IF(AND([Risk Likelihood]@row =…
