SUMIF function not returning all data

Question

_{Thursday, May 9, 2013 3:38 PM}

My SUMIF functions are not returning all data.  The result is a partial sum of the data specified in the criteria.  The SUMIF function is summing 4 out of 6 cells.  My SUMIF function is on a separate page from my ranges. My "Range" and "Sum Range" share a worksheet. I copied the data onto a separate worksheet to use in conjunction with the first, which returned an different incorrect result.  This worksheet was converted from Excel 2003. Any advice.

Thanks,

Kewood

All replies (8)

_{Thursday, May 9, 2013 4:15 PM ✅Answered}

Post your formula, and a sample of your data. It could be as simple as trailing spaces, or as complex as..... just about anything.

_{Thursday, May 9, 2013 5:40 PM ✅Answered | 1 vote}

Bernie was right.  When I copied your data to Excel, I noticed that the last two lines were "Fuel  " F-U-E-L-space-space, instead of just the word.  I'd suggest inserting a column of TRIM() referring to your column "A" data as the "Range" field for your SUMIF.  That will remove extraneous spaces.

Good luck.

-jerrykern

_{Thursday, May 9, 2013 5:50 PM ✅Answered | 1 vote}

TRIM() may work, but it ignores non-breaking spaces which is a common problem with data queries and so may not help in this case.

What will work in all cases is

=-SUMIF('GL AP DATA'!$A:$A,"*Fuel*",'GL AP DATA'!$C:$C)

The *Fuel* will find any cell with Fuel in it, like "BioFuel" and not just extra spaces so make sure that you are sure of the uniqueness of what you are looking for.

_{Thursday, May 9, 2013 3:52 PM}

This could be about a million different things, but my first attempt to fix this type of problem is to look at how the data is formatted in the "Range" field.  If you have numbers formatted as text, or something similar, SUMIF has issues figuring that kind of thing out.

Good luck.

-jerrykern

_{Thursday, May 9, 2013 5:01 PM}

Thank you, my formula: =-SUMIF('GL AP DATA'!$A:$A,"Fuel",'GL AP DATA'!$C:$C)

My data (generated by a query from external data: 

The SUMIF formula is located on a separate worksheet as the data.  The data worksheet is 'GL AP DATA'

This function worked fine in Excel 2003 and I have converted to Excel 2010.  The result should be 7,968.00, but my result is 6,771.58.  It is not picking up the last two numbers. I have formatted the data in column 3 (C) to make sure it is a number.

Thanks so much.

Kewood

_{Thursday, May 9, 2013 5:46 PM}

Hmmmm.  Not sure if this is an artifact of my copying the data from a webpage, but TRIM() was not working well.  You may need to try:

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

-jerrykern

_{Thursday, May 9, 2013 6:09 PM}

Thank you both so much. I manually typed the word Fuel over my query results and that brought a correct result to my SUMIF function.  I then put *Fuel* in my SUMIF function and it resolved my problem with the other line items I was trying to pull from.

Again - thanks so much.

Kewood

_{Thursday, May 9, 2013 6:11 PM}

That is to say that I changed the remaining SUMIF functions to include '*' and it corrected the problem with my other categories. Thanks.