formula

Sue van Niekerk

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

Bassam Khalil

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 Khalil

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:

Sue van Niekerk

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 :-)

Bassam Khalil

@Sue van Niekerk

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

Yes at your service to explain anything you need, please call me and we can arrange for a zoom meeting to answer all your questions and solve your formula:

My Email: Bassam.k@mobilproject.it

Sue van Niekerk

Morning Bassam,

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

Thanks for the inspiration

Bassam Khalil

@Sue van Niekerk

Excellent, i will be happy to help you any time.

Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Bassam Khalil

@Sue van Niekerk

Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"