Hey all
I have been putting together a new report and using the Sumproduct function in windows excel 2003. I have written a formula that works but I need it to do one more thing and it has me scratching my head.
The Formula:
=SUMPRODUCT( --('Cost Recovery Report'!$T$2:$T$20000=A6),--('Cost Recovery Report'!$B$2:$B$20000="North Harbour"),--('Cost Recovery Report'!$Q$2:$Q$20000="DC Location"),'Cost Recovery Report'!$R$2:$R$20000)
The problem:
Where it says "North Harbour" this part is looking in the column B for anything that matches the words North Harbour but I need it to also bring back anything that says North Harbour or Havant. So in other words put the multiple figures together in column R which match both North Harbour and Havant in column B.
I am thinking it is just a simple way of putting "North Harbour" & "Havant" but this is not working.
Any ideas from fellow excel peeps would be appreciated and when working earn a beer at the lan. :)
Hey Gortex
SUMPRODUCT expects an array and that function only returns a single value so trying to use OR will cause the problem you're having.
So one way to get around this is:
=SUMPRODUCT( --('Cost Recovery Report'!$T$2:$T$20000=A6),--('Cost Recovery Report'!$B$2:$B$20000="North Harbour"),--('Cost Recovery Report'!$Q$2:$Q$20000="DC Location"),'Cost Recovery Report'!$R$2:$R$20000)+SUMPRODUCT( --('Cost Recovery Report'!$T$2:$T$20000=A6),--('Cost Recovery Report'!$B$2:$B$20000="Havant"),--('Cost Recovery Report'!$Q$2:$Q$20000="DC Location"),'Cost Recovery Report'!$R$2:$R$20000)
Not particularly efficient but it gets the job done. How many times will you be using this formula in your sheet?
Thanks will try that tomorrow as finished for the day now :)
I am using it a good few times prob around 200 times but due to the size of the report it is going to be easier with that formula rather than a pivot table or something like that. Will let you know how it works out.
Beer on standby....
and ??
Yep worked well and fixed the small problem. Going to add it to my new thing learned as am sure it will come useful again.
Now just need to write the documentation for it. Shame there is no simple formula for that :)
Nice work TwoBad!