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 Jess M. Your Own Question
Jess M.
Jess M., Computer Support Specialist
Category: Software
Satisfied Customers: 6967
Experience:  Computer Software Support specialist for more that 10 years
49766785
Type Your Software Question Here...
Jess M. is online now

I am trying to put a formula into an Excel 2013 spreadsheet

Resolved Question:

I am trying to put a formula into an Excel 2013 spreadsheet that was used in an Excel 2003 spreadsheet, but the result of the formula is coming up with #VALUE! The formula is an array formula using the SUMPRODUCT function. Is there a difference in the way a formula has to be entered in Excel 2013?
Submitted: 3 years ago.
Category: Software
Expert:  Jess M. replied 3 years ago.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

Because the formula is an array, you need to press CTRL+SHIFT+Enter to execute the formula. Then the formula bar shall display the formula with the curly braces {}.

Have you tried that?


Please let me know so that I can help you further.

Best regards,
Jess
Customer: replied 3 years ago.

Where do I need to press ctrl + shift + enter? I have tried this in the cell which contains the formula but nothing has happened.

Expert:  Jess M. replied 3 years ago.
Jaquie,

Yes, you need to press Ctrl+Shift+Enter when you EDIT the cell containing the formula. Then if the keystrokes are successful, when you visit or click on the cell again, you shall see that the formula is now enclosed by curly braces.

See my screen shot below:
Customer: replied 3 years ago.

I have done that and the curly braces are now around the formula, but the result is still showing as #VALUE! Would it help if I gave you the formula?

Expert:  Jess M. replied 3 years ago.
Jaquie,

Yes, it will be helpful. However, it will also be helpful if you can give or send me a dummy or sample of your Excel file so that I can check what causes the error to appear. The error can be caused of any cells used in the formula that are blank.

If you can send a dummy of our file with dummy or few sample data, you can send it to me by uploading to http://wikisend.com and then give me the download link or the 6-digit File ID.

Regards,
Jess
Customer: replied 3 years ago.

I have uploaded the file - the 6 digit ID is 514426.


 


The formula is =SUMPRODUCT((Income!$A$3:$A$334>=DATEVALUE(D1))*(Income!$A$3:$A$334<=DATEVALU(D2)),Income!$AA$3:$AA$334)


 


Kind regards


 


Jaquie

Expert:  Jess M. replied 3 years ago.
Jaquie,

I got your file. Is this formula found in cell D5 in Monthly Summary sheet? I am looking at it now.

Jess
Customer: replied 3 years ago.

Yes it is.


 


Jaquie

Expert:  Jess M. replied 3 years ago.
Jaquie,

Thank you for patiently waiting. As I checked the file and the error, it appears that the error is caused by the datevalue function you used in the formula. The reason for this is bacause, D1, already contains a "date" entry so Excel already sees its numeric value equivalent. If you want to test, even if you enter the formula in any empty cell, =datevalue(d1), you will get the #value error.

Thus, you just need to remove the datevalue function like this:

=SUMPRODUCT((Income!$A$3:$A$334>=D1)*(Income!$A$3:$A$334<=D2),Income!$AA$3:$AA$334)

Here is the modified file:
http://wikisend.com/download/241130/Accounts 2014-15 dummy_rev1.xlsx

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated! Cool

If you need further assistance, please do not rate me negatively. Instead, please reply to me so that I can help you further.

Thank you!

Best regards,
Jess
Jess M. and other Software Specialists are ready to help you