+ Reply to Thread
Results 1 to 14 of 14

SUMIF not working properly

  1. #1
    Registered User
    Join Date
    01-13-2004
    Posts
    42

    SUMIF not working properly

    Hello,
    I have a simple sumif formula and I noticed it's not adding up properly. In some cases, it's only adding up the first instance it sees a criteria.

    =SUMIF($F$16:$F$66,K16,$H$16:$I$66)

    K16 for example has 1 in it. F16 to F66 has 1 a few times and , 2s, 3s e.t.c.
    But the formula is only adding up the 1st 1. I have checked if have formated columns H to I as currency and so they are not in text.
    any ideas?

    Thank you.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: SUMIF not working properly

    If the sumif isn't adding properly, it's a problem in column F. Make sure THAT column has the same data type as K16.

    Click GO ADVANCED and use the paperclip icon to post up your workbook showing the misbehaving data so we can look directly.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: SUMIF not working properly

    Try =Sumproduct(($F$16:$F$66=K16)*($H$16:$I$66)).
    Also note that it's possible that if the numbers came from an outside source, simply formatting them as currency won't make them currency. Try putting 0 in a cell, copying it, selecting H16:I66 and pasting special-->Add. This will force those values to numbers.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIF not working properly

    SUMIF works on a 1 to 1 basis only, which means that the sum range must be the same size as the criteria range, which means your formula will only sum column H, try using SUMPRODUCT like this

    =SUMPRODUCT(($F$16:$F$66=K16)*$H$16:$I$66)

  5. #5
    Registered User
    Join Date
    01-13-2004
    Posts
    42

    Re: SUMIF not working properly

    Thank you, the sumproduct formula worked.
    do you mind telling me why the sumif didn't work in this instance? I didn't have to use the pastespecial value suggestion you made...

    Thanks again.

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: SUMIF not working properly

    JB, that's not the whole issue here. With SUMIF, it doesn't recycle the matches from column F. In other words, it will read 51 values and sum the 51 values from H, ignoring those from I. See simple attached workbook. Sumproduct does recycle the values, using the matches from H twice and thus giving the correct answer.

    *Edit: A bit slow. DLL beat me to the punch.
    Attached Files Attached Files

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: SUMIF not working properly

    Hi, the problem lies with the sum_range argument. Your range argument is a one column reference, but your sum_range is a two column reference. Although range and sum_range don't have to be the same shape and size, in this case, sumif will only sum the first column of the sum_range.

    You would need to do something like this to catch both columns:

    =SUMIF($F$16:$F$66,K16,$H$16:$H$66)+SUMIF($F$16:$F$66,K16,$I$16:$I$66)

    hth

  8. #8
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: SUMIF not working properly

    DLL, this is from the 2010 help files:

    The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using the upper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument. For example:

    Please Login or Register  to view this content.
    Is this new in 2010?

  9. #9
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: SUMIF not working properly

    No, it isn't. I keep forgetting this feature, but it's around in 2007 and I think someone here has pointed out before that it's in 2003 as well. It doesn't change the point, though, that if the criteria range is only one column, the sum range will be, too, regardless of what you set it as.

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIF not working properly

    Yeah,

    SUMIF hasn't changed at all. I think the wording of the help files is a little misleading. When it says

    "The sum_range argument does not have to be the same size and shape as the range argument"

    that means you can specify the range to be anything you want......but, as the examples make clear, the sum range is determined solely by the size and shape of the criteria range...and the top left cell of the specified sum range.

    In Excel 2007 AVERAGEIF works the same way too, but in the SUMIFS and AVERAGEIFS functions Excel forces you to explicitly define all ranges with the same size and shape, otherwise you get an error
    Last edited by daddylonglegs; 01-08-2010 at 06:22 PM.

  11. #11
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: SUMIF not working properly

    I think the wording of the help files is a little misleading.
    They really managed to make a lot of words around the fact that SUMIF will only sum a sum_range of the same shape and size of range.

  12. #12
    Registered User
    Join Date
    12-09-2018
    Location
    chicago, illinois
    MS-Off Ver
    office 365
    Posts
    7

    Re: SUMIF not working properly

    I'm having the same issue but with text to product conversion, working with office 365, I should have a cumulative value for my categories of 403, yet excel will only count 284. How do I get all 403 values, by category? I'm using sumif and in the formula: sumif(A:A,"category",B:B). This should get me me a value of lets say jeep, which is 33, but excel spits out with the above formula of 18. why is it missing the other 15 cumulative values?
    Attached Files Attached Files

  13. #13
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: SUMIF not working properly

    Some of the values are "jeep " with a trailing space, not "jeep".
    Rory

  14. #14
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: SUMIF not working properly

    lloyd
    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

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

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ 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