SUMIF Formula populating wrong data

Highlighted
New Contributor

My SUMIF Formula works in my entire workbook except there are certain cells where it's pulling the wrong data.  Attached is a sample subset.  I've tried ensuring the cells are in the same format, etc.  I use SUMIF extensively so I'm not sure what the issue is.  Any thoughts?  

7 Replies
Highlighted

It looks like it calculates correctly.

 

Here is SUMIF result

image.png

and here is if filter your table and calculate manually

image.png

Highlighted

Sorry, I attached the wrong file.  

Highlighted

D M,

 

read this and praise Lori :-)

Lori's solution for the COUNTIF bug

 

Highlighted

I'm not following on how to correct this with a soft hyphen.  

Highlighted

Detlef, thank you. Just in case here is another workarounds https://exceljet.net/formula/count-long-numbers-without-countif

Highlighted
=SUMIF(data!A:A,CHAR(173)&Total!A5,data!B:B)
Highlighted

Thank you.  Adding the CHAR(173) worked.  Although not sure why but it's fixed!  Thanks!