Excel look up formula

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:32 PM GMT

QuestionEdit

     I am hoping you will be able to help me out.

I have a spreadsheet which has an area code in Column (A) i.e. 12345 and a list of jobs for each area with find dates in column (B) and repair dates in column (C) i.e. 1/1/1900 some of these jobs also have N/A in this column if the job has been repaired by a third party. On another tab within the spreadsheet I have a column (D) showing how many jobs for each area code are open i.e. not repaired yet, but am trying to get a formula to tell me that if any jobs are unrepaired display "Open Jobs" if the jobs are all repaired then display the most recent date of repair or if all jobs have N/A the most recent find date.

AnswerEdit

sounds reasonably simple, if potentially a long formula - is it possible to see the sheet (or a mock up of it) - my email being aidan.heritage@virgin.net - it sounds like a multiple condition countif (which can be done with sumproduct prior toversion or countifs inonwards) to determine if jobs are open, and a maximum value on certain criteria being true if all are N/A.

Advertisement

©2024 eLuminary LLC. All rights reserved.