SUMIF being used to compare differences between lists, not working correctly

[Cookdem]

Hello, I have having an issue with SUMIF being used to compare 2 lists.

I have a 2 sets of invoice numbers and totals from different sources that I am using to compare and see which invoices are missing from either list and to have a total of what is missing.

When doing the SUMIF, it seems to work one way but not the other when copy and pasting as values . I'm aware there is likely a formatting issue (as per the green triangle in column R) but I'm not sure what it is or how to fix it as I purposely formatted both lists using the TEXT formulas in column E and L.

Important formulas to note:
Column E: =TEXT(D3,"#") and dragged down to bottom of list
Column L: =TEXT(K3,"#") and dragged down to bottom of list
Column P: =SUMIF(E:E,O3,F:F)-SUMIF(L:L,O3,M:M) and dragged down to bottom of list
Column R: =SUMIF(E:E,R3,F:F)-SUMIF(L:L,R3,M:M) and dragged down to bottom of list

Thanks in advance for any help!

 

[StephenCrump]

"Unexpected" results often occur in Excel when values that look the same are in fact different, because one is text and one is numeric. Formatting a text value as numeric does not convert it from text to numeric. Nor does formatting a number as text change it from numeric to text.

But I don't think that's the problem here, as I would expect SUMIF() to treat any text value that looks like a number as a number.

It's not clear how you get your invoice numbers into Column O. Are you sure these invoices, starting with 8483327, exist in List 2? If this invoice number does appear in List 2, let's say at H100, then compare O3 and H100 carefully.

Perhaps you could prepare a small sample file to illustrate (stripped of any sensitive information), upload to a drop box and post the link so we could take a look?