# Excel Formula Help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### SportsFan15

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!

#### Keebellah

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

#### SportsFan15

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.

#### Keebellah

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.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On