# formula

Options

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

• ✭✭✭✭✭✭
Options

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:

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

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:

bassam.khalil2009@gmail.com

• Options

HI Bassam

my completion days is using a formula (=NETDAYS)

I dont understand the use of the symbols, - why symbols, and do you have a formula for the symbols?

Could you explain in " smart sheet for Dummies" way :-)

• ✭✭✭✭✭✭
edited 07/31/21
Options

I used symbol because you can't use highlights as a criteria.

My Email: Bassam.k@mobilproject.it

bassam.khalil2009@gmail.com

• Options

Morning Bassam,

I got it working :-) ..... some midnight oil and lots of coffee hahahah

Thanks for the inspiration

• ✭✭✭✭✭✭
Options

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options