SUMIF not calculating properly when opening file

[lborja]

Hi Everyone,

I have a SUMIF formula that works about 90% of the time.

=SUMIF(B$6:W$36,AB23,D$6:W$36) where (AB23) is "PE", the initials for a building. What I want to calculate is adding the amount in the data range wherever it sees "PE". For the most part, it does work. However, when I am reconciling, I notice that sometimes it does not read "PE" and does not do the addition. However, when I re-type "PE" in the data range, it suddenly works and adds up again.

Any idea why that is?

[HangMan]

Check to see whether there are any additional spaces before or after any of the PEs, in your range e.g. "PE " or " PE". This would cause the associated values not to be included in your SUMIF and would also explain why when re-typing PE manually the values are then included.

[lborja]

That would definitely cause some issues. I will keep an eye out for this in the future and see if this is the error some of my colleagues are making.

[FDibbins]

1 way to help prevent possible typo's is to use Data Validation...kind of like a drop-down box containing the words you need to pick from. It is fairly simple to set up, and as long as you dont have a ton of options