+ Reply to Thread
Results 1 to 7 of 7

Sumifs formula not working

  1. #1
    Registered User
    Join Date
    09-16-2009
    Location
    NW Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    24

    Sumifs formula not working

    =AVERAGEIFS($D$106:$D$2825,$D$106:$D$2825,">"&$I117,$D$106:$D$2825,"<"&$J117)

    Returns = 0

    But on a copy of the same worksheet, the same formula returns the correct value. Is there something wrong with my formula cotext?

    Thank you,

    Todd
    Last edited by todd.graff@gmail.com; 09-21-2009 at 02:26 PM. Reason: Fixed

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Sumifs formula not working

    Are you sure the values in D are numbers and not numbers stored as text ?

    What does

    =COUNT(D106:D2825)

    return ?

    If 0 run Text to Columns on this range to coerce to number
    (assuming they are constants and not result of formulae)

  3. #3
    Registered User
    Join Date
    09-16-2009
    Location
    NW Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Sumifs formula not working

    Yes they are formatted as numbers. I should also add that if I hardcode my < and >, the formula works.
    Last edited by todd.graff@gmail.com; 09-21-2009 at 01:28 PM.

  4. #4
    Registered User
    Join Date
    09-16-2009
    Location
    NW Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Sumifs formula not working

    =COUNT(D106:D2825)
    returns 2720

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Sumifs formula not working

    Might be easier if you could post up the offending sheet in a sample file ?

    When you said about the copy - did you copy the entire sheet or just the values onto another sheet ?

  6. #6
    Registered User
    Join Date
    09-16-2009
    Location
    NW Arkansas
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Sumifs formula not working

    My problem is still with M110-M121.

    Thanks for the assistance.
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Sumifs formula not working

    M won't calculate given it is dependent upon N being > 0 and N can never be greater than 0 given it counts how many cells are > J and < K and J > K. I think perhaps you need to correct N in the first instance, perhaps to reference I & J rather than J & K ?
    ie the issue with the AVERAGEIFS calc is not in fact the AVERAGEIFS function at all.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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