Tech Support Guy banner
Status
Not open for further replies.
1 - 4 of 4 Posts

·
Registered
Joined
·
6 Posts
Discussion Starter · #1 ·
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!
 

·
Trusted Advisor
Joined
·
6,911 Posts
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
 

·
Registered
Joined
·
6 Posts
Discussion Starter · #3 ·
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.
 

·
Trusted Advisor
Joined
·
6,911 Posts
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.
 
1 - 4 of 4 Posts
Status
Not open for further replies.
Top