Solved

help with Sumproduct

Posted on 2015-01-06
8
87 Views
Last Modified: 2015-01-08
I am getting a return of #N/A from the below sumproduct

I have sheet called Raw Data where Column A is ClientName,   Column C is Ccy,  Column AF is YesNo and Column Z is GBP

I have a sheet sheet Called Monthly Revenue where Client Name is in Column M Currency is in Column N and
The sumProduct is in Column R


=SUMPRODUCT((ClientName=M5)*--(Ccy=N5)*--(YesNo="YES"),GBP)

This is returning #N/A

Could an expert help me out here please
0
Comment
Question by:Jagwarman
8 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 40533711
That looks OK syntax-wise (although you don't need * and --, use just *) but I guess #N/A is caused by ranges not being the same size - how are ClientName, Ccy, YesNo and GBP defined? They should ideally all be based on the same column so that they will all be the same length.

If you are using Excel 2003 or earlier then SUMPRODUCT can't use whole columns, so make sure that the named ranges are equal sizes but also not the whole column

If you have Excel 2007 or later then SUMIFS is preferable, i.e.

=SUMIFS(GBP,ClientName,M5,Ccy,N5,YesNo,"Yes")

.....but you still need to get the ranges the same size (or you'll get #VALUE! error with that version)

regards, barry
0
 
LVL 26

Expert Comment

by:pony10us
ID: 40533754
You are using two separate sheets?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40533759
Further to my reply above.....

Note that #N/A error could also be caused if you have #N/A errors in the referenced ranges. You'll need to get rid of them if you want SUMPRODUCT to work, although SUMIFS will ignore #N/A errors in some places

regards, barry
0
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 125 total points
ID: 40533810
Can you try with this one?

=SUMPRODUCT((ClientName=M5)*(Ccy=N5)*(YesNo="YES")*(GBP))
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 125 total points
ID: 40535491
The above replies have assumed that you do have named ranges for each of the columns and you are not just referring to the column header.

If not convert the formula to include reference ranges rather than Column headers:

=SUMPRODUCT(('Raw Data'!$A$1:$A$500=M5)*('Raw Data'!$C$1:$C$500=N5)*('Raw Data'!$AF$1:$AF$500="YES"),'Raw Data'!$Z$1:$Z$500)

Adjust the 500 row value to what is required but as Barry has mentioned, don't use whole columns.

If you want to carry on with above syntax, there are a couple of ways that you can just refer to the column header:

1) Convert data to a table and then rewrite the formula, selecting the relevant entries will convert the column references to include square brackets [  ] around the column references.
2) There used to be a setting in Options whereby you could tell Excel to allow column and/or row headers within the formula but I can't find the equivalent in Excel 2010.

Thanks
Rob H
0
 

Author Comment

by:Jagwarman
ID: 40535870
All, what I have subsequently found is that although I set 'Named' ranges from i.e. Client Name range A1 to A64000
when I go to use it next time the range has now changed to ='Raw Data'!$A1048569:$A63992

Clearly this is one reason why this is not working.

Any ideas please
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 40536076
When you define the range make sure that you use $ in front of both row numbers and column letters to make it completely absolute.......otherwise this type of problem can occur.

If the range is not absolute then it changes relative to the position of the cursor. If the range moves "upward", as yours appears to have done then the resultant range is now the same as ='Raw Data'!$A63992$:A1048569, which is clearly not the same size as your other ranges, hence #N/A error as I correctly diagnosed above.

If you use SUMIFS function as I suggested above then there's no performance downside to using the whole column as the named range.

regards, barry
0
 

Author Closing Comment

by:Jagwarman
ID: 40538161
All very helpful thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now