Jagwarman
asked on
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=N 5)*--(YesN o="YES"),G BP)
This is returning #N/A
Could an expert help me out here please
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
This is returning #N/A
Could an expert help me out here please
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are using two separate sheets?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All very helpful thanks