# Excel Formula Help

Discussion in 'Business Applications' started by SportsFan15, Nov 18, 2011.

Not open for further replies.

Joined:
Sep 14, 2011
Messages:
6
Hi,

I am currently working in an excel file where I am having problems with the following formula:
=SUMPRODUCT(--('Forecast Detail'!\$E\$5:\$E\$9409='Data Dump'!\$B3049),'Forecast Detail'!\$J\$5:\$J\$9409)

A little more background on the file I am working with:
- This is a rather large file that I've had to turn calculations on manual so that I am able to work in it without the calculations constantly going
- The data dump cell that it is referencing it to is a concatenation formula combining a cost center and a G/L account. This results in a 18 digit number.
- The forecast detail column E is also a concatenation formula of the same thing.
- The forecast detail column J is the real dollars for the month that goes through the various cost centers and G/L accounts.

I am then trying to pull all of the data that is in the forecast detail sheet that fits the criteria in my formula. However, there is a couple instances where it isn't matching the correct concatenation formula. Does anyone know how I could correct this or if there is a better formula to get the same result accomplished?

Thanks!

Joined:
Mar 27, 2008
Messages:
6,560
First Name:
Hans
Is this an IF condition? (the red text)
=SUMPRODUCT(--('Forecast Detail'!\$E\$5:\$E\$9409='Data Dump'!\$B3049),'Forecast Detail'!\$J\$5:\$J\$9409)

This will return either True or False so I don't see what you're trying

Joined:
Sep 14, 2011
Messages:
6
Keebellah-

This formula is producing numbers and not just true or false.

When I tried using the Sum(IF() array function (using ctrl shift enter) it only came up as #values so that's why I have used the sumproduct formula.

Joined:
Mar 27, 2008
Messages:
6,560
First Name:
Hans
Still the part I marked in red will either return a True or False, what you are saying there that A=B so nothing can be calculated, try each part on an empty cell first to see the results and then start combining.

Debugging my friend, that's what you need to do now.

As Seen On