Hi, I have a sumproduct formula which is returning an #N/A error, and wondered if someone can work out why.
I have attached the workbook and the desired result - but basically I would like to populate E6:E10 with values from the Engage tab.
The result in E6 should be - 3741 and E7 should be - 2834
Is it because i am referencing a linked cell A1?
Any suggestions? many thanks
Last edited by Blake 7; 11-12-2014 at 10:39 AM.
Blake 7
If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.
http://failblog.org/
I think you may be correct in your assumption. Using the formula evaluator (ALT+T+U+F), your second constraint that references cell A1 on the Summary Sheet returns all "FALSE" for the match criteria, which would be a contributor as to why you are receiving a #N/A error.
Spread the love, add to the Rep
"None of us are as smart as all of us."
U can try SUMIFS Function u will get what u r looking for...
Thanks - Naveed
-----------------------------
If the suggestion helps you, then Click * to Add Reputation
To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
Hi, Try this
Formula:
Please Login or Register to view this content.
☚ Click ★ just below left if it helps, Boo?ath?
Hi Guys, thanks for your responses - i'll try the suggestions and post back with the results so someone else may benefit, many thanks for your time.
Hi, ref the attachment with references locked..
Hi Boopa - thanks for uploading a workbook. It seems to work but when i flick between tabs or do something on the work book the values zero themselves!! I've never seen this before! any suggestions / ideas?
Hi Probably the cell linking may causing the problem
try the below ('Engage 2014'!$B$2:$B$19=Summary!$A$1+0)
Formula:Please Login or Register to view this content.
Hi - its definitely the linked cell causing the issue as i just did a few tests. I'll try your suggestion and post back... many thanks
Perfect ! can you explain how the +0 helped in this situation? many thanks!
Hi Blake 7
I tried F9 on ('Engage 2014'!$B$2:$B$19=Summary!$A$1) and found it gave False for all conditions. I noticed the dates in the Engaged page are formatted slightly differently so I changed the date in A1 from 28/09/2014 to 28.09.2014, tried F9 again and it returned True for the first 3 places.
In my test, combining this with boopathirajas first formula gave the correct, stable answers.
Regards
Hi Bob, thanks for your input here - much appreciated
Originally Posted by Blake 7
the formula treating the cell A1 as text, adding the zero forced it to evaluate as number
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks