Excel Formula query

Started by Gortex, July 13, 2011, 05:08:35 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Print
Go Down Pages1
User actions

Gortex

July 13, 2011, 05:08:35 PM
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.  :)
"The oldest and strongest emotion of mankind is fear, and the oldest and strongest kind of fear is fear of the unknown." "Never explain anything."

TwoBad

#1
July 13, 2011, 06:05:10 PM
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?
The Battle of Damnation Alley, 25th January 2015
\'You are hereby awarded the Military Cross, posthumously, for an act of exemplary gallantry during active operations against the enemy on land.\'
[SIGPIC][/SIGPIC]

Gortex

#2
July 13, 2011, 06:48:55 PM
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....
"The oldest and strongest emotion of mankind is fear, and the oldest and strongest kind of fear is fear of the unknown." "Never explain anything."

TwoBad

#3
July 16, 2011, 12:48:03 PM
and ??
The Battle of Damnation Alley, 25th January 2015
\'You are hereby awarded the Military Cross, posthumously, for an act of exemplary gallantry during active operations against the enemy on land.\'
[SIGPIC][/SIGPIC]

Gortex

#4
July 17, 2011, 11:28:35 PM
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 :)
"The oldest and strongest emotion of mankind is fear, and the oldest and strongest kind of fear is fear of the unknown." "Never explain anything."

TeaLeaf

#5
July 18, 2011, 09:21:16 AM
Nice work TwoBad!
TL.
Wisdom doesn\'t necessarily come with age. Sometimes age just shows up all by itself.  (Tom Wilson)
Talent wins games, but teamwork and intelligence wins championships. (Michael Jordan)
Print
Go Up Pages1
User actions