• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 125
  • Last Modified:

help with Sumproduct

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
Jagwarman
Asked:
Jagwarman
4 Solutions
 
barry houdiniCommented:
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
 
pony10usCommented:
You are using two separate sheets?
0
 
barry houdiniCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ProfessorJimJamCommented:
Can you try with this one?

=SUMPRODUCT((ClientName=M5)*(Ccy=N5)*(YesNo="YES")*(GBP))
0
 
Rob HensonIT & Database AssistantCommented:
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
 
JagwarmanAuthor Commented:
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
 
barry houdiniCommented:
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
 
JagwarmanAuthor Commented:
All very helpful thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now