# SUMIF Keeps Coming Up as Zero!

#### Sabiosa

##### New Member
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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### barry houdini

##### MrExcel MVP
What's the exact formula you're using?

In what way are the results not correct?

#### Sabiosa

##### New Member
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

##### Well-known Member
Please post the actual formula to help determine if it is flawed in some way.

#### Sabiosa

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

#### gaynard_nelson

##### Active Member
Sabiosa said:
=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

##### Well-known Member
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
EFGHI
11abc10abc10
12abc10abc10
131020
14
Sheet1

#### Oaktree

##### MrExcel MVP
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.

Replies
10
Views
290
Replies
19
Views
614
Replies
3
Views
152
Replies
5
Views
155
Replies
4
Views
246

1,196,021
Messages
6,012,906
Members
441,740
Latest member
Latrs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back