1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula Help

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

Thread Status:
Not open for further replies.
Advertisement
  1. SportsFan15

    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!
     
  2. Keebellah

    Keebellah Trusted Advisor

    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
     
  3. SportsFan15

    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.
     
  4. Keebellah

    Keebellah Trusted Advisor

    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
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027371

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice