How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
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
A new question is answered every 9 seconds

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: 4 years ago.
Category: Microsoft Office
Expert:  John D replied 4 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 4 years ago.

 

 

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

example result;

 

10 Chicago

11 Madison

16 Greensborough

 

4 with active dates

 

41 total students

 

Expert:  John D replied 4 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

John D and 2 other Microsoft Office Specialists are ready to help you
Expert:  John D replied 4 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 4 years ago.

 

 

first namelast nameschoolactive dateinactive date
jimjohnsonChicago3/2/2012
John JacksonMadison2/17/20123/29/2012
FrankWinnerJohnsburgh1/4/2012
KenJonesChicago12/20/20112/12/2012

Count Chicago students active

Count Madison students active

Count Johnsburgh students active

 

Count total inactive students

 

 

Count total students list (without duplicates)

 

Does this help?

 

 

 

 

Expert:  John D replied 4 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

 

 

John D and 2 other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.

 

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

Expert:  John D replied 4 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 4 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.

My problem is the same as before I received your answer, your formula is not working.

Expert:  John D replied 4 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 4 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 4 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!