Help with SUMIFS function (I think related to mis-matched data type)

twarner

I'm writing a SUMIFS formula using cross-sheet references. Some rows work and some don't. I think it may be due to a data type mis-match, but can't figure out how to fix that. Here is a section of the sheet:

The formula in "Original Estimate (MDE)":

=SUMIFS({Application Estimates by PID - Estimate Cost}, {Application Estimates by PID - PID}, PID@row, {Application Estimates by PID - MOTS ID}, [MOTS ID]@row, {Application Estimates by PID - Estimate Reason}, "MDE", {Application Estimates by PID - Estimate Status}, "Validated")

My thinking is that some PID values are strictly numerical (i.e. 413076) and some have a letter at the end (i.e. 405698a). So, I've tried ensuring the PID is evaluated as TEXT by doing this:

=SUMIFS({Application Estimates by PID - Estimate Cost}, {Application Estimates by PID - PID}, (PID@row + ""), {Application Estimates by PID - MOTS ID}, [MOTS ID]@row, {Application Estimates by PID - Estimate Reason}, "MDE", {Application Estimates by PID - Estimate Status}, "Validated")

But that has no effect.

In the referenced sheet ("Application Estimates by PID"), I've also tried converting the PID to ensure it is TEXT in the same way. I've created a helper column with : =[PID]@row + ""

That also has no effect.

I'm stumped as to how to get this to work for alpha-numeric values. Any ideas?

Thanks,

Tony

Carson Penticuff

I have setup a couple of sheets in the same manner as you describe and your original formula works as expected. As for as the PID formatting... I can't imagine their being an issue with the mixed int/text cells. If there were to be an issue, I would think it would be with the strictly numeric cells. I do notice all entries in the MOTS ID column are left aligned. Is that column left aligned, or is it possible there is some mixed formatting there?

twarner

Carson - thanks for your input. The MOTS ID column is left aligned, but all of the values are integers of various lengths. I'm dumbfounded as to what may be causing the issue still.