Formula for coinciding columns...

[Schwimms]

I have 3 columns, the first one has project numbers, the second one has
dates, and the third has my formula. What I want the formula to do is for any
project number that is the same in column 1 and has all dates filled in for
those projects to show "complete" in the third column. Then if there are
projects with the same number but there are some blanks in the dates, then
the third row will return incomplete. Ex:

Projects Dates Status
444 1/1/08 Complete
444 1/3/08 Complete
444 2/5/08 Complete
554 Incomplete
554 3/7/07 Incomplete
554 Incomplete

 

[T. Valko]

Try this:

=IF(SUMPRODUCT(--(A$2:A$7=A2),--(B$2:B$7<>""))=COUNTIF(A$2:A$7,A2),"Complete","Incomplete")

Copy down as needed

 

[David Biddulph]

=IF(COUNTIF(A$2:A$7,A2)=SUMPRODUCT((A$2:A$7=A2)*(B$2:B$7<>"")),"Complete","Incomplete")

 

[Schwimms]

WOW Wow Weee Wow!

That worked thank you!

Try this:

=IF(SUMPRODUCT(--(A$2:A$7=A2),--(B$2:B$7<>""))=COUNTIF(A$2:A$7,A2),"Complete","Incomplete")

Copy down as needed

 

[T. Valko]

You're welcome. Thanks for the feedback!