SUMIF Keeps Coming Up as Zero!

[Sabiosa]

Why is that I can only get SUMIF to produce the desired result only like 10% of the time. I do it THE SAME WAY each time... what gives?
What am I missing?

I appreciate any guidance I can get on this - it's KILLING ME!!!!

-Sabrina

 

[barry houdini]

What's the exact formula you're using?

In what way are the results not correct?

 

[Sabiosa]

3 different types of purchases: ops, equip, rec

All on one spreadsheet. I want it to automatically add up the different dollar amount coinciding with the different types of purchases. People enter in their purchases all day long... and so everytime they put in their entry I want it to update the TOTAL amount spent on ops, equip, rec etc.

SUMIF
Range being column where we write ops, equip, rec
Criteria being "the word" I am having it look up
Sum Range being the column with the dollars

 

[babycody]

Please post the actual formula to help determine if it is flawed in some way.

 

[Sabiosa]

=SUMIF(F18:F180,"21T1",G18:G180)

 

[gaynard_nelson]

=SUMIF(F18:F180,"21T1",G18:G180)

The entries in column F must match the formula precisely, in this case 21T1, no spaces or other characters of any kind allowed. Column G must have number entries, no text or non number type entries. Most likely the people doing the entries are doing some of this.

 

[babycody]

Yes I have had spaces mess with a lot of formulas. To see if this is your problem use =Trim(cell address here) Where the cell address is the address of one of the words you are looking up. You can do this to a small sample where all the words are used. Copy from the trimmed cells and paste special>values into the range the trim() function was refering to. Now use =SUMIF(F18:F180,"21T1",G18:G180) but adjust the ranges so that only the cells you trimmed the spaces out of are refered to. If the formula then works you may need to trim all, or atleast find the word that is causing the problem. Using Data Validation List to limit what can be entered into a cell would help this.

E11 in this example has a trailing space. I fixed this with Trim()

Book6
	E	F	G	H	I
11	abc	10	abc	10
12	abc	10	abc	10
13		10		20
14
Sheet1

 

[Oaktree]

Alternatively, you could try:

=SUMIF(F18:F180,"*21T1*",G18:G180)

Where *'s are used as wildcards. Note that this would sum 21T1S and A21T1 as the same value, but if your part numbers are unique, this may get you the result you're after without having to change the underlying data.