Sumif frustrations

[juliaharry]

Hi,

I really like the sumif and sumifs but sometimes it just does not work as the cells i am trying to pick up are not found due to the criteria cell not matching although visually they both look the same.
What steps can i take logically to solve this problem. Any shortcuts keys would be helpful to do this.
Thanks
Julia

 

[T. Valko]

Hi,

I really like the sumif and sumifs but sometimes it just does not work as the cells i am trying to pick up are not found due to the criteria cell not matching although visually they both look the same.
What steps can i take logically to solve this problem. Any shortcuts keys would be helpful to do this.
Thanks
Julia

See this:

http://contextures.com/xlFunctions02.html#Trouble

It describes common data problems. Even though it's about a different function the problems are universal in scope.

 

[Aladin Akyurek]

Hi,

I really like the sumif and sumifs but sometimes it just does not work as the cells i am trying to pick up are not found due to the criteria cell not matching although visually they both look the same.
What steps can i take logically to solve this problem. Any shortcuts keys would be helpful to do this.
Thanks
Julia

Non-printable chars (like trailing spaces) around the target entries are often the culprit.

A diagnostic test can be:

=SUMIF(A2:A10,E2,B2:B10)

vs

=SUMIF(A2:A10,"*"&E2&"*",B2:B10)

 

[T. Valko]

Instead of "masking" the problem by using convoluted formulas it's better to fix the problem at the source.

 

[juliaharry]

Thanks for this, I have gone through and had a close look, I think one of my problems is importing files as text instead of CSV.
Hopefully no more frustrations!

 

[Aladin Akyurek]

Thanks for this, I have gone through and had a close look, I think one of my problems is importing files as text instead of CSV.
Hopefully no more frustrations!

Thanks for the feedback. One thing though: A formula for a diagnostic test is a formula for diagnostic text, that is, for problem diagnosis. Such formulas can even be the way out when nothing can be done about the input.

 

[T. Valko]

Thanks for this, I have gone through and had a close look, I think one of my problems is importing files as text instead of CSV.
Hopefully no more frustrations!

Good deal. Thanks for the feedback!