• 100% Satisfaction Guarantee
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now

# Microsoft Excel 2007 I need a formula that will read a specific

### Customer Question

Microsoft Excel 2007

I need a formula that will read a specific name in "d" if there is a date in Column "e" and if no date in column "f"?
Submitted: 5 years ago.
Category: Microsoft Office
Expert:  John D replied 5 years ago.

Hi,

Copy the following formula and paste it in G1, then use Fill Down to fill it down to as many cell as you need

=IF(AND(OR(CELL(&quo t;format",E1)={"D1","D2","D3","D4","D5"})=TRUE,OR(CELL("format",F1)={"D1","D2","D3","D4","D5"})=FALSE),D1,"")

Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the solution

Customer: replied 5 years ago.

If the name in "d" is different how will the formula know to count cell

example result;

10 Chicago

16 Greensborough

4 with active dates

41 total students

Expert:  John D replied 5 years ago.
hmm.. You did not ask for "count initially". You said " read a specific name", and that was the basis if the formula that I gave you

Thank you for accepting, and I will be happy to help you with the new "count" requirement

Expert:  John D replied 5 years ago.

Thanks

So you want to count the number of occurrence in column D where 1) the corresponding values in E are dates, and 2) the corresponding values in E are not dates. Correct?

Customer: replied 5 years ago.

 first name last name school active date inactive date jim johnson Chicago 3/2/2012 John Jackson Madison 2/17/2012 3/29/2012 Frank Winner Johnsburgh 1/4/2012 Ken Jones Chicago 12/20/2011 2/12/2012

Count Chicago students active

Count Johnsburgh students active

Count total inactive students

Count total students list (without duplicates)

Does this help?

Expert:  John D replied 5 years ago.

Ok here is the formula that counts Chicago active (Chicago should be in column D, and the dates in E and F)

=SUMPRODUCT((D:D="Chicago")*(E:E>0)*(F:F=0))

To count other names replace "Chicago" with the name that you would like to count

Hope this helps

Customer: replied 5 years ago.

The formula has a Circular Reference Warning message and the answer seems to be zero over and over.

Expert:  John D replied 5 years ago.

The problem is not with the formula. You either have placed the formula in the wrong cell or you have other issues in that file. I can send you a sample file to show you how it works

However if you want me to check your file and diagnose the problem you will need to open a new question and assign an appropriate amount.

Thank you

Customer: replied 5 years ago.

I don't understand;

However if you want me to check your file and diagnose the problem you will need to open a new question and assign an appropriate amount.

Expert:  John D replied 5 years ago.

Ok send me the file today and I let you know if the issue is with the formula or something else.

Customer: replied 5 years ago.

Attachment: 2012-04-19_040432_off_campus1.xlsx

I tried the formula in the Greater Chicago total, see attached excel document.

Expert:  John D replied 5 years ago.

You cannot put the formula in the same columns that it refers to. The formula refers to F:F while the formula itself is in column F. Place the formula in another column and you will be good to go

I hope this helps

Remember, if for any reason you are unsatisfied with my answer, we can continue the conversation until you’re satisfied and Accept my answer. I can address follow up questions at no extra charge and I’m always here to help. Thank you for using JustAnswer!