SUMIF not calculating, giving sum as ZERO

Question

HI! I am having issue with SUMIF function. It is working fine on SOME cells, but not others. 

I have checked that all the cells in a column are formatted the same (all TEXT or all NUMBERS) hoping that would fix the issue. It did not. I'm at a loss as to the issue as I have more than triple checked the formula at this point. 

Here you can see cell C184 (and in C185) where the SUMIF formula is working and calculating perfectly:  formula is =SUMIF(B2:B181,"Director",C2:C181) and the value I change in subsequent cells is simply the text 'criteria' in quotes. 

Here, you can see that in C186 that the formula is NOT calculating, despite the formula being identical except for the change in the TEXT "criteria". I did the same in C185, and C189 and they all work perfectly. but this Cell, and two others, just Calculate every time to ZERO. 

I even started all over from scratching a new document to see if the corrected 'formating' (as text vs. number for example) of the cells was lingering in the cells or something. Nada. STILL did not work in random cells that I formulated. 

What am I missing? Can anyone help me out? I've searched online for hours and tried all the suggestions i have found, but nothing is working. I need to set up the entire sheet of columns for six rows with totals and need to fix this bug!

Thanks in advance for any advice. 

Tammy

Answer 1

Hi TammyMichelle,

Also, check and remove any extra spaces before and after the text. It could be reason why your criteria and criteria range do not match.

Hope it helps you sort this out. Please let me know if you have any further questions.

Regards,

John

Answer 2

Hi TammyMichelle,

I am John, an Independent Advisor and a Microsoft user like you. I'm here to work with you on this issue.

Please try this formula in cell C184:

=SUMIF($B$2:$B$181,B184,$C$2:$C$181)

Then copy/drag down this formula to fill the rest of the column with data.

Note: I used dollar sign ($) to make an absolute cell reference that can't be changed even if formula is copied to another cell.

I hope it worked out well for you. If not, please reply back to this forum to let me know. I will be glad to follow up and help you.

Regards,

John

Answer 3

Hi 

IMHO

It might be due to some extra space characters in the cells in column "B" that makes the values not match the criteria in the formula.

Kindly suggest you

1. Use cell references for the criteria, instead of manually typing them in the formula,
2. also lock the ranges in the formula using $ sign to keep the absolutes the ranges as you copy down
3. I personally prefer in these cases the SUMIFS() function

Please, try the formula in the cell C184 and copy/drag it down

=SUMIFS($C$2:$C$181,$B$2:$B$181,B184)

I hope this helps you

Regards

Jeovany

Answer 4

Dear John, 

THANK YOU! It was spaces that were AFTER my text that were the issue. The formula you gave did not work as the criteria needed to change each time in 6 repeating rows, however, locking the value with the $ sign was a great reminder and I am doing my formulas that way now. 

CHEERS! and stay safe and healthy! 

Tammy

Answer 5

Dear Jeovany, 

Thank you kindly for your help! It was, indeed, a pesky space unseen AFTER the text value. Also, Using the $ sign is a better way and that is a helpful reminder. 

All the best to you and wishes for a safe and happy fall! 

Tammy