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

Thread Starter
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!
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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
 

SportsFan15

Thread Starter
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.
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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.
 
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top