+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT Formula

  1. #1
    Registered User
    Join Date
    07-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    SUMPRODUCT Formula

    I'm having a heck of a time trying to figure out this formula.
    I'm trying to calculate YTD operating expenses as a percent of sales.
    the only twist is I need to subtract depreciation expense from operating expense.

    I don't know why but my formula is resulting in a HUGE number.

    Please see attached.

    Thanks!

    The formula is in cell C12 and should equal to C21
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT Formula

    It's the order of operations.

    The formula you have in C22 to show how your math is done
    =-(-800+70)/C17

    However, the formula you put in C12 is actually doing
    =-800--70/C17

    With the ()'s omitted, devision and multiplication are done before addition and subraction.
    So it's doing
    =-800-(-70/C17)

    The -70/C17 is done first.

    You need to add those () to the formula in C12, and add a - to the front
    Try

    =-(SUMPRODUCT(($B$4:$B$8="Total Operating Expenses")*($C$3:$N$3<=$A$1)*($C$4:$N$8))-SUMPRODUCT(($B$4:$B$8="Total Depreciation")*($C$3:$N$3<=$A$1)*($C$4:$N$8)))/SUMPRODUCT(($B$4:$B$8="Total Revenue")*($C$3:$N$3<=$A$1)*($C$4:$N$8))

  3. #3
    Registered User
    Join Date
    07-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: SUMPRODUCT Formula

    OMG....I feel.....dumb.....but thanks so much!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT Formula

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula into VBA (Sumproduct)
    By milo1984 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-03-2015, 09:46 AM
  2. [SOLVED] Sumif Formula or Sumproduct Formula? Three Criteria.
    By oHUTCHYo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 10:50 AM
  3. Sumproduct formula
    By Belfast Stu in forum Excel General
    Replies: 14
    Last Post: 01-06-2010, 09:51 AM
  4. Sumproduct/formula help?
    By wykey87 in forum Excel General
    Replies: 5
    Last Post: 07-15-2009, 08:48 AM
  5. Sumproduct formula
    By 3smees23 in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 01-07-2009, 03:05 PM
  6. SUMPRODUCT formula
    By rjmills18 in forum Excel General
    Replies: 5
    Last Post: 10-03-2008, 07:58 AM
  7. Sumproduct formula help
    By Daniel Bonallack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1