+ Reply to Thread
Results 1 to 11 of 11

SUMIF and SUMPRODUCT issues

  1. #1
    Registered User
    Join Date
    07-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    28

    SUMIF and SUMPRODUCT issues

    What am I doing wrong that G3165 won't sum the Regions that have AK? The sum should be 731,449. Little help?Sum Help.xlsx

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

    Re: SUMIF and SUMPRODUCT issues

    It's a circular reference..

    =SUMIFS(G:G,F:F,A3165)
    But you put the formula in G3165 (which is within G:G)
    A formula can't refer to itself..

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIF and SUMPRODUCT issues

    Hi,

    The circular reference is your issue.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: SUMIF and SUMPRODUCT issues

    Since you are summing the entire column of G, it would seem to be a circular reference problem.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  5. #5
    Registered User
    Join Date
    07-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: SUMIF and SUMPRODUCT issues

    Quote Originally Posted by Jonmo1 View Post
    It's a circular reference..

    =SUMIFS(G:G,F:F,A3165)
    But you put the formula in G3165 (which is within G:G)
    A formula can't refer to itself..
    Thank you - I did that but it still comes back as 0. ????

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

    Re: SUMIF and SUMPRODUCT issues

    Quote Originally Posted by djbcktt View Post
    Thank you - I did that but it still comes back as 0. ????
    You did what?


    Note, you will still get 0 if you just put the formula in a different column.
    You must also 'resolve' all the circular references.

    The existance of a circular reference can halt future calculations.

    so if you still have the same formula in G3165, but just add the formula somewhere else, you still have a circular reference in the original cell.
    The new formula will still return 0 until you 'resolve' the circular reference in G3165
    Last edited by Jonmo1; 08-19-2013 at 05:26 PM.

  7. #7
    Registered User
    Join Date
    07-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: SUMIF and SUMPRODUCT issues

    See the attached. I used the formula you suggested, but it still comes back as 0. I can't figure it out!


    Sum Help.xlsx

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

    Re: SUMIF and SUMPRODUCT issues

    Quote Originally Posted by djbcktt View Post
    See the attached. I used the formula you suggested, but it still comes back as 0. I can't figure it out!


    Attachment 259055
    I didn't suggest a formula.
    In fact, i didn't suggest any solution at all in my first post (#2)
    I only pointed out what the problem was.

  9. #9
    Registered User
    Join Date
    07-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: SUMIF and SUMPRODUCT issues

    I apologize - I took your =SUMIFS(G:G,F:F,A3165) to be a solution.

    Any ideas on what I can do to get this to sum correctly?

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: SUMIF and SUMPRODUCT issues

    g3165 =SUMIF($F$2:$F$3164,$F3165;$G$2:$G$3164)
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: SUMIF and SUMPRODUCT issues

    You need to move that blue area to a new sheet. It seems like some kind of summary. Above it you have directly entered values, but the summary formulas include the whole columns, thus including the formulas themselves in the formulas: circ ref errs. You could also leave it as is but change the formulas from G:G, F:F to G2:G3100, etc.
    Attached Files Attached Files
    Ben Van Johnson

+ 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. Possible Sumproduct or similar issues
    By arthurphil in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-07-2013, 10:27 AM
  2. sumproduct issues
    By Dyakin in forum Excel General
    Replies: 6
    Last Post: 03-29-2012, 10:03 AM
  3. Issues with Sumproduct
    By Chareth Cutestory in forum Excel General
    Replies: 6
    Last Post: 09-02-2011, 06:08 PM
  4. sumproduct issues
    By tweety127 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2008, 11:38 AM
  5. SUMPRODUCT issues
    By Ashlynn Grace in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-26-2006, 05:00 PM

Tags for this Thread

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