| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for coinciding columns...
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula for coinciding columns...
Try this:
=IF(SUMPRODUCT(--(A$2:A$7=A2),--(B$2:B$7<""))=COUNTIF(A$2:A$7,A2),"Complete","Inc omplete") Copy down as needed -- Biff Microsoft Excel MVP "Schwimms" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula for coinciding columns...
=IF(COUNTIF(A$2:A$7,A2)=SUMPRODUCT((A$2:A$7=A2)*(B $2:B$7<"")),"Complete","Incomplete")
-- David Biddulph "Schwimms" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula for coinciding columns...
WOW Wow Weee Wow!
That worked thank you! "T. Valko" wrote: Try this: =IF(SUMPRODUCT(--(A$2:A$7=A2),--(B$2:B$7<""))=COUNTIF(A$2:A$7,A2),"Complete","Inc omplete") Copy down as needed -- Biff Microsoft Excel MVP "Schwimms" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula for coinciding columns...
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Schwimms" wrote in message ... WOW Wow Weee Wow! That worked thank you! "T. Valko" wrote: Try this: =IF(SUMPRODUCT(--(A$2:A$7=A2),--(B$2:B$7<""))=COUNTIF(A$2:A$7,A2),"Complete","Inc omplete") Copy down as needed -- Biff Microsoft Excel MVP "Schwimms" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for columns | Excel Discussion (Misc queries) | |||
| what formula to get total from two different columns | Excel Worksheet Functions | |||
| formula to stay fixed on two columns as you add more columns | Excel Discussion (Misc queries) | |||
| If formula using 2 or more columns | Excel Worksheet Functions | |||
| Formula that looks at two columns | Excel Worksheet Functions | |||
Linear Mode
