Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Sum product formula

=SUMPRODUCT(--('Consolidated Data'!$C:$C="CCL - Cash Management")---('Consolidated Data'!$AB:$AB="Credit")*('Consolidated Data'!$L:$L))

This is returning a value error.

i am trying to get the credit amount of  CCL - Cash Management in that sheet. Col L contains the amounts

Can anyone help?

Thanks
Seamus
ASKER CERTIFIED SOLUTION
Avatar of Wim_Bl
Wim_Bl
Flag of Belgium 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
Avatar of Seamus2626

ASKER

Thanks Wim_bl, but that is still giving me value errors, i cant use the character ";" in sumproduct, it rejects it outright

Any other suggestions?

Thanks
SOLUTION
Avatar of aflockhart
aflockhart
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 could remove the column title, if that is the problem, or exclude row 1 from the ranges being multiplied and added.

Also re ";" comment above - depending on your locality you may need to use commas rather than semicolons to separate the parameters.
=SUMPRODUCT(---('Consolidated Data'!$C3:$C65001="CCL - Cash Management"),--('Consolidated Data'!$AB3:$AB65001="Debit"),--(('Consolidated Data'!$AA3:$AA65001=1)*('Consolidated Data'!$AC3:$AC65001)))

That works for me, but for some reason is multiplying the sums by -1, so where i should return

26,455,422   i get
-26,455,422

How is this happening?!

Thanks
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
You have three minus signs at the start inside SUMPRODUCT, you only need two.
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
Thank you all, thats all been very helpful