Hi Guys

I am needing help with another formula,

in another sheet I want to extract data from the sheet above

I want to get an average of days (green Highlighted amounts)

is IF DEPT contains REF what is the average of Green Highlighted amounts) and the same for NIS and SSS

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Sue van Niekerk

    Hope you are fine, are you highlighted completion days manually or you are using a type of conditional formatting rule, because you can't use the highlight as a criteria in the formula,but if you exchange the highlight with symbol column ( RYG ) then your formula will be as following:

    =SUMIFS([completion days]:[completion days], Dept:Dept, @cell = "REF", 
    Highlight:Highlight, @cell = "Green") / COUNTIFS(Dept:Dept, @cell = "REF", 
    Highlight:Highlight, @cell = "Green")

    the following screenshot shows the result:

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!