# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  SUMIF not working properly

## opeyemi1

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.

----------


## JBeaucaire

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.

----------


## darkyam

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.

----------


## daddylonglegs

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)

----------


## opeyemi1

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.

----------


## darkyam

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.

----------


## teylyn

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

----------


## teylyn

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?

----------


## darkyam

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.

----------


## daddylonglegs

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

----------


## teylyn

> 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_.

----------


## llyod276

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?

----------


## rorya

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

----------


## Fotis1991

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.

----------

