Link to home
Start Free TrialLog in
Avatar of Jagwarman
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=N5)*--(YesNo="YES"),GBP)

This is returning #N/A

Could an expert help me out here please
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jagwarman
Jagwarman

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
All very helpful thanks