excluding the blanks

[flomo]

I have two columns in my worksheet one is Course the other in attendenc
as a percentage.

I want to work out the overall attendence for each course.

I have worked out how to sum the attendence for a particular cours
using SUMIF.

=SUMIF(AD7:AD159,"=Beauty",AC7:AC159)

I then need to divide this by the total number of people registered o
Beauty to get the overall attendence. This should be simple but ther
are some people for whom no attendence is recorded on the Beaut
course. I need to exclude these from the count.

Somethiing like "Count all those registered on beauty for who
attendence is not blank"

any help much appreciated,

Flo

 

[Vasant Nanavati]

=SUMPRODUCT((AD7:AD159="Beauty")*(AC7:AC159<>""))

 

[flomo]

Thanks,

That works for most but for some reason I really can't fathom i
doesn't work when the course is "Art".

Some one else suggested that I have a hidden column next to th
attendence with 1 if there is an attendence and 0 if otherwise. Then
can sum that colum. It is a bit of a hack but I kow it will work.

I'm sure your way would have been prettier

Flo