Solved

help with Sumproduct

Posted on 2015-01-06
8
92 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
LVL 32

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel 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 the scrolling table in Microsoft Excel using the INDEX function.

831 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