cant quite figure this one out so hoping and expert on here will be able to!


In simple terms...


Column A has project names

Column B has a 2 char code in it

Column C has a % complete stat (and is blank if the project has not yet started)


I need to total up and then average the % complete where the 2 char code in column B = a certain value.

For example, for any cell in column B that contains EI I want the average value across column C.

I have the following formula for that....

=SUMIF($B$4:$B$30,$F4,C$4:C$30)/COUNTIF($B$4:$B$30,F$4) (where F4 = my 2 char code)

Now this works a treat BUT I want to only divide it by projects that have actually started and have a % value (ie not blank or 0).

Any idea how to achieve this? I hope that made sense!