I know someone is going to show me a very easy way to do this but for some reason I can't figure this out. I have a large spreadsheet of department projects worked on by various people. The last two columns are NAME and COMPLETION DATE. I need a formula to figure completion percentages based on each person. I have used =COUNTIF(AE:AE,"JOHN") to figure the total for each person. I need a formula to count each "COMPLETION DATE" cell for an individuals name that is filled. Any help would be greatly apprecaited.

Try something like:
=SUMPRODUCT((AE:AE="JOHN")*(AF:AF<> 0))
where AF is the column containing the completion dates.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

An alternative to avoid processing the entire column:

=COUNTIFS(AE:AE, "JOHN", AF:AF, ">0")

Hi shg
long time no see since EF
Do you mean SUMP... processes entire columns while countifs will stop when there is no more data?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Right.

COUNTIF(S) and SUMIF(S) functions, unlike most others, don't allow arrays as arguments; they require ranges. So it's easy for them to intersect the ranges passed against the used range of the worksheet. So you can pass full-column references without worrying about the consequences.