How JustAnswer Works:
• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee
Ask Eric Ewing Your Own Question
Eric Ewing, IT Pro
Category: Microsoft Office
Satisfied Customers: 211
Experience:  I am a technology professional with 18 years experience, and hold 15 industry certifications (from hardware/software repair to mobility to IT Security, and everything in-between!).
93256567
Type Your Microsoft Office Question Here...
Eric Ewing is online now

# Need a formula in excel.That in cell R it should

### Customer Question

Need a formula in excel.That in cell R it should calculate the following if it finds the words "Amazon - P" in cell H and the number amount higher than 5 in cell L it should add add a dollar to cell R.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Eric Ewing replied 1 year ago.
Hi Customer! My name is ***** ***** I am an independent computer expert who's been paired with you through JustAnswer.com. It is my pleasure to assist you today.Might it be possible for you to attach the Excel file to this conversation so as that I can take a look? Barring that, here's a down-and-dirty formula that might work:*This will work ONLY if your cell R is empty, and you are wanting the value there to be either \$1.00 or nothing. If your cell R has an existing value, you cannot conditionally take that value and then add \$1.00 to it lest you create a circular reference (you would need to write a macro script to handle the calculation, in that case).*First, make sure your column R is set to the ACCOUNTING number format, so that it displays dollars and cents. Then paste this into R:=IF(AND((H#="Amazon - P"),(L#>5)),1,"")You will need to, of course, replace each"#" above with your corresponding row number. As you copy & paste the formula into other rows, it will update the row numbers accordingly.Will that work for you?
Customer: replied 1 year ago.
R is empty and the formula is not working at all
Customer: replied 1 year ago.
Oh i need to replace the #
Expert:  Eric Ewing replied 1 year ago.
Yep, that should help. ;-)
Expert:  Eric Ewing replied 1 year ago.
Any luck?
Customer: replied 1 year ago.
Its not working,let me rephrase, Only if it finds "Amazon - P" and in column L the number is ***** than 5 should it add one dollar to column R
Customer: replied 1 year ago.
And mind you, column R is empty rite now so I will be fine with your formula but i don't think it was built correctly.
Expert:  Eric Ewing replied 1 year ago.
That's correct. It works on my end exactly as described. Can you attach the spreadsheet for me to take a look at?
Expert:  Eric Ewing replied 1 year ago.
If you'd prefer, we can setup a remote help session whereby I can connect to your computer and work on the formula directly; however, that does cost a little extra and I could do it at zero extra cost if you're able to upload the file here.
Customer: replied 1 year ago.
Expert:  Eric Ewing replied 1 year ago.
Received! Thanks. Just a moment or two.
Customer: replied 1 year ago.
I think i figured it out, hold on.
Customer: replied 1 year ago.
Its working.
Expert:  Eric Ewing replied 1 year ago.
Awesome! I tried this out in your spreadheet:=IF(AND((H274="Amazon - P"),(L274>5)),"\$1.00","")Replacing with the appropriate row numbers. Is that what you also have?
Customer: replied 1 year ago.
Is there a way to add that if its anything else the number should be 0.00
Customer: replied 1 year ago.
Because if not, i cant use that cell to calculate further stuff, it comes up invalid value
Expert:  Eric Ewing replied 1 year ago.
Absolutely! Just add \$0.00 into the last set of quotation marks. So, it should look similar to this:=IF(AND((H274="Amazon - P"),(L274>5)),"\$1.00","\$0.00")
Expert:  Eric Ewing replied 1 year ago.
Hi Customer: It's been a bit since we chatted...I just wanted to check back in and make sure the formula I provided worked for your purposes? Please let me know!