SUMIF & COUNTIF and omitting blanks

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!

Hopefully this example will show what I want to acheive. In D11 I want to show the average % (column d) for all those with the code in column C that matches the criterea (D13).

But, you will see that row 7 matches the criterea but it has no % value (it's blank) so I want this one ingonred from the average calculation becuase this project has not started yet.

Any ideas?

worked a treat, thanks!