Solved

help with Sumproduct

Posted on 2015-01-06
8
107 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: 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!

 
LVL 26

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 33

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

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

710 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