Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

help with Sumproduct

Posted on 2015-01-06
8
Medium Priority
?
124 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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 27

Assisted Solution

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

=SUMPRODUCT((ClientName=M5)*(Ccy=N5)*(YesNo="YES")*(GBP))
0
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 500 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 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

916 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